Python PSQL.run_sql_file方法代码示例

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def test_06_deletetable_visimap_for_uao_tables(self):
     tinctest.logger.info("-------------------------------")
     tinctest.logger.info("test_06 Verify that the visimap updates with delete row in uao table test run")
     tinctest.logger.info("-------------------------------\n")
     out_file = os.path.join(self.outpath, "deletetablevisimapinfo_06.out")
     sql_file = os.path.join(self.sqlpath, "deletetablevisimapinfo_06.sql")
     ans_file = os.path.join(self.anspath, "deletetablevisimapinfo_06.ans")
     sql_cmd1 = "drop table if exists uao_visimap_test06 ;create table uao_visimap_test06 (i int, j varchar(20), k int ) with (appendonly=true) DISTRIBUTED BY (i);\n"
     sql_out = PSQL.run_sql_command(sql_cmd=sql_cmd1)
     self.assertIsNotNone(re.search("CREATE TABLE", sql_out))
     sql_cmd2 = "\\pset tuples_only\n\\pset footer off\nSELECT relfilenode FROM pg_class WHERE relname='uao_visimap_test06';\n"
     with open(sql_file, "w") as f:
         f.write(sql_cmd2)
     sql_out = PSQL.run_sql_file(sql_file=sql_file, out_file=out_file, flags="-q")
     with open(out_file, "r") as f:
         relid = f.read()
     aovisimap_cmd = "select count(*) from gp_dist_random('pg_aoseg.pg_aovisimap_%s');\n" % relid.strip()
     sql_cmd3 = (
         "select * from uao_visimap_test06;\n"
         + aovisimap_cmd
         + "insert into uao_visimap_test06 select i,'aa'||i,i+10 from generate_series(1,5) as i;\ndelete from uao_visimap_test06 where i=3;\nselect * from uao_visimap_test06;\n"
         + aovisimap_cmd
     )
     with open(sql_file, "w") as f:
         f.write(sql_cmd3)
     sql_out = PSQL.run_sql_file(sql_file=sql_file, out_file=out_file, flags="-q")
     assert Gpdiff.are_files_equal(out_file, ans_file)

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
    def do_test(self, timeout=0, sqlfile=None, host=None, port=None, username=None, password=None, flags='-a', usetemplate=False):
        """
        @summary: Run a PostGIS test case
        
        @param timeout: Number of seconds to run sql file before timing out
        @param sqlfile: The path to sql file (relative to TEST.py directory)
        @param host: The GPDB master host name to use to connect to database
        @param port: The GPDB port used to make connections to the database
        @param username: The database username to use to connect to the database
        @param password: The password for the database user used to connect to database
        """
        if sqlfile is None:
            testcase = inspect.stack()[1][3].split('test_')[1]
            
            #file = mkpath(testcase +".sql")
            file = local_path(testcase +".sql")
        else:
            #file = mkpath(sqlfile)
            file = local_path(sqlfile)
        # run psql on file, and check result
        #psql.runfile(file,timeout=timeout,host=host,port=port,username=username,password=password,flag=flags)
        #self.checkResult(ifile=file, optionalFlags=" -B")

        out_file = local_path(testcase + ".out")
        ans_file = local_path(testcase +".ans")
        PSQL.run_sql_file(sql_file = file, out_file = out_file)
        self.assertTrue(Gpdiff.are_files_equal(out_file, ans_file))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def _generate_explain_analyze_output(self):
     """
     execute explain analyze output for a given query
     """
     ea_sql_file = os.path.join(self.get_out_dir(), os.path.basename(self.sql_file).replace('.sql','_explain_analyze.sql'))
     with open(ea_sql_file, 'w') as o:
         with open(self.sql_file, 'r') as f:
             explain_write = False
             for line in f:
                 if not line.startswith('--') and not explain_write:
                     #keep all the GUCs
                     o.write('-- start_ignore\n')
                     for guc_string in self.gucs:
                         o.write("SET %s;" %guc_string)
                         o.write(line)
                     for orca_guc_string in self.orcagucs:
                         o.write("%s;\n"%orca_guc_string)
                     # Add gucs to print optimization time to log
                     o.write("SET optimizer_print_optimization_stats=on;\n")
                     o.write("SET client_min_messages='log';\n")
                     o.write('-- end_ignore\n')
                     o.write('explain analyze %s' %line)
                     explain_write = True
                 else:
                     o.write(line);
     ea_out_file = ea_sql_file.replace('.sql','.out')
     PSQL.run_sql_file(ea_sql_file, dbname = self.db_name, out_file = ea_out_file)
     with open(ea_out_file, 'r') as f:
         self._ea_output = f.read()

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
    def do_test(self, timeout=0, sqlfile=None, host=None, port=None, username=None, password=None, flags='-a', ans_version=False):
        """
        @summary: Run a test case
        
        @param timeout: Number of seconds to run sql file before timing out
        @param sqlfile: The path to sql file (relative to TEST.py directory)
        @param host: The GPDB master host name to use to connect to database
        @param port: The GPDB port used to make connections to the database
        @param username: The database username to use to connect to the database
        @param password: The password for the database user used to connect to database
        """
        (gpdb_version, build) = self.gpdb.GetGpdbVersion()
        if sqlfile is None:
            testcase = inspect.stack()[1][3]
            filename = testcase.split('test_')[1]
            sql_file = local_path(filename +".sql")
            out_file = local_path(filename + ".out")
            ans_file = local_path(filename + ".ans")
        else:
            sql_file = local_path(sqlfile)
            out_file = local_path(sqlfile.split('.')[0] + '.out')
            ans_file = local_path(sqlfile.split('.')[0] + '.ans')
        if ans_version:
            (gpdb_version, _) = self.gpdb.GetGpdbVersion()
            if gpdb_version.startswith('4.3'):
                ans_file = ans_file+'.4.3'

        init_file = local_path('init_file')
        init_file_list = []
        init_file_list.append(init_file)

        # run psql on file, and check result
        PSQL.run_sql_file(sql_file=sql_file, out_file=out_file, timeout=timeout, host=host, port=port, username=username, password=password,flags=flags)
        self.assertTrue(Gpdiff.are_files_equal(out_file, ans_file, match_sub=init_file_list))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def do_test_fixture(self, fixture):
     """
     @summary: Runs a setup or teardown routine
     
     @param fixture: Set to either 'setup' or 'teardown'. Used to determine sql file suffix.
     """
     testcase1 = inspect.stack()[1][3] 
     testcase = self.id().split(".")[2]
     init_file = local_path('init_file') 
     init_file_list = []
     init_file_list.append(init_file)
     if fixture == 'setup':
         sqlfile = local_path(testcase + "_setup.sql")
         outfile = local_path(testcase + "_setup.out")
         ansfile = local_path(testcase + "_setup.ans")
     elif fixture == 'teardown':
         sqlfile = local_path(testcase + "_teardown.sql")
         outfile = local_path(testcase + "_teardown.out")
         ansfile = local_path(testcase + "_teardown.ans")
     else:
         raise Exception("do_test_fixture(): Invalid value for fixture. Acceptable values are 'setup' or 'teardown'")
     
     # check if setup sql file exists
     if os.path.isfile(sqlfile):
         # if exists, run setup sql, and validate result
         PSQL.run_sql_file(sql_file = sqlfile, out_file = outfile)            
         Gpdiff.are_files_equal(outfile, ansfile, match_sub=init_file_list)
     else:
         pass

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def persistent_Rebuild(self, hostname = None, port = None, type = 'Master'):
     ''' Rebuild Persistent Object by connecting in Utility mode '''
     sql_file = local_path('persistent_Rebuild_%s.sql'%type)
     now = datetime.datetime.now()
     timestamp = '%s%s%s%s%s%s%s'%(now.year,now.month,now.day,now.hour,now.minute,now.second,now.microsecond)
     out_file = sql_file.replace('.sql', timestamp + '.out')
     PSQL.run_sql_file(sql_file = sql_file,  PGOPTIONS = '-c gp_session_role=utility', host = hostname, port = port, out_file = out_file) 

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
    def test_run_sql_file_wth_username(self):
        sql_file = os.path.join(os.path.dirname(inspect.getfile(self.__class__)),'test.sql')
        username = getpass.getuser()
        self.assertTrue(PSQL.run_sql_file(sql_file = sql_file, username = username))

        #Invalid username
        self.assertFalse(PSQL.run_sql_file(sql_file = sql_file, username = 'invalidusername'))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
	def test_outof_shmm_exit_slots(self):
		"""
		The issue of MPP-19973 is that a shmem exit callback to reset 
		a temporary namespace is not removed when the temporary namespace is
		reset.

		In situations, where a temporary namespace is multiple times reset
		because of an exception in a subtransaction, the callbacks
		use up all shmem_exit slots.
		"""

		sql_setup_file = local_path('mpp19973_setup.sql')
		PSQL.run_sql_file(sql_file=sql_setup_file)

		# Test case setup verification
		self.assertTrue(table_exists("foo"))
		self.assertTrue(function_exists("testfn"))
	
		sql_file = local_path('mpp19973.sql')
		out_file = local_path('mpp19973.out')
		PSQL.run_sql_file(sql_file=sql_file,
			out_file=out_file, output_to_file=True)

		# There will be different error messages in the output, but
		# we should not run out of shmem exit slots.
		self.assertNotRegexpMatches(open(out_file).read(), 
			"out of on_shmem_exit slots",
			"Database should not run out of shmem_exit slots")

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def setUpClass(cls):
     """
     @description: Create the databases and roles to be used in the test
     """
     PSQL.run_sql_file(local_path('setup.sql'), dbname='postgres')
     dsp = DspClass()
     dsp.add_user()

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def test_vacuum_appendonly(self):
     out_file = os.path.join(self.output_dir, 'vacuum_ao_co.out')
     ans_file = os.path.join(self.ans_dir, 'vacuum_ao_co.ans')
     sql_file = os.path.join(self.sql_dir, 'vacuum_ao_co.sql')
     PSQL.run_sql_file(sql_file, out_file=out_file)
     if not Gpdiff.are_files_equal(out_file, ans_file):
         raise Exception('Vacuum table failed for append only tables !')

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def test_43_alter_table_with_oid(self):
     '''MPP-13870: Alter table Set Without Oids fails in case of inheritance'''
     sql_file = local_path('alter_table_with_oid.sql')
     out_file = local_path('alter_table_with_oid.out')
     ans_file = local_path('alter_table_with_oid.ans')
     PSQL.run_sql_file(sql_file = sql_file, out_file = out_file)
     self.assertTrue(Gpdiff.are_files_equal(out_file, ans_file))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
    def setUpClass(cls):
        super(EnhancedTableFunctionTest, cls).setUpClass()

        tinctest.logger.info("*** Running the pre-requisite sql files drop.sql and setup.sql")
        PSQL.run_sql_file(local_path('sqls/setup/drop.sql'))
        PSQL.run_sql_file(local_path('sqls/setup/create.sql'))
        tinctest.logger.info("*** Starting the Enhaced table test")

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def doQuery(self, sqlfile, default=''):
     sql_file = local_path(sqlfile)
     filename_prefix = sqlfile.split('.sql')[0]
     out_file = local_path(filename_prefix + '.out')
     ans_file = local_path(filename_prefix + '.ans')
     PSQL.run_sql_file(sql_file = sql_file, out_file = out_file)
     self.assertTrue(Gpdiff.are_files_equal(out_file, ans_file))

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def test_with_concurrent_workload(self):
     """
     add new mirrors while concurrent workload in progress, check that mirrors added
     and current workload won't get affected, in the end, run checkmirrorseg.
     Note that: adding mirrors while running workload has checkmirrorseg issue with MPP-24311
     """
     gprecover = GpRecover()
     self._setup_gpaddmirrors()
     self._cleanup_segment_data_dir(self.host_file, self.mirror_data_dir)
     sql_setup_file = local_path('sql/ao_heap_table_setup.sql') 
     sql_file = local_path('sql/ao_heap_table.sql')
     pg_stat_activity = 'SELECT * FROM pg_stat_activity;'
     PSQL.run_sql_file(sql_setup_file)
     subprocess.Popen(["psql", "-f", sql_file])
     time.sleep(15)
     subprocess.Popen(["gpaddmirrors", "-ai", self.mirror_config_file, "-d", self.mdd])
     time.sleep(15)
     result = PSQL.run_sql_command(pg_stat_activity, flags='-q -t', dbname='template1')
     result = result.strip()
     rows = result.split('\n')
     self.assertTrue(len(rows) > 1)
     while len(rows) > 1:
         result = PSQL.run_sql_command(pg_stat_activity, flags='-q -t', dbname='template1')
         result = result.strip()
         rows = result.split('\n')
         time.sleep(3)
     gprecover.wait_till_insync_transition()
     self.verify_config_file_with_gp_config()

# 需要导入模块: from mpp.lib.PSQL import PSQL [as 别名]
# 或者: from mpp.lib.PSQL.PSQL import run_sql_file [as 别名]
 def test_01(self):
     "SPI: plpgsql"
     sql_file = local_path("query01.sql")
     out_file = local_path("query01.out")
     ans_file = local_path("query01.ans")
     PSQL.run_sql_file(sql_file=sql_file, out_file=out_file)
     self.assertTrue(Gpdiff.are_files_equal(out_file, ans_file))