Codeschnipsel

Hin und Wieder brauche ich mal eine Ablage für Codeschnipsel, welche ich nicht soo oft brauche, als dass ich sie auswendig kenne, mir jedoch einen (vermeintlich) großen Nutzen bringen können.

WARNUNG

Diese Seite ist nur für mich gedacht!
Wenn Ihr sie lest und versteht, ist das ein netter Zufall ;-)

ganz Allgemein

prozentuale Abweichung:

40 statt 50 = -20%
--> ((40/50) -1) * 100
--> (0,8 - 1) * 100
--> -0,2 * 100
==> -20

((soll/ist) -1) * 100

MySQL

  • Rownum:

SET @rownum=0;
SELECT (@rownum:=@rownum+1) AS row_number, domain from domains

  • byte_format:

select bytes, LOG2(bytes), concat_ws(' ',round(bytes/pow(2,floor(log2(bytes)/10)*10),2) , 
case (floor(log2(bytes)/10)) 
  when 1 then 'KiB'
  when 2 then 'MiB'
  when 3 then 'GiB'
  when 4 then 'TiB'
  else 'bytes' end) as formattedSize  

  • byte_format (Function):

CREATE FUNCTION byte_format (bytes BIGINT) RETURNS CHAR(50)
  RETURN LPAD(
  CASE WHEN bytes<1024 
  THEN CONCAT(bytes, ' bytes') 
  ELSE CONCAT_WS(' ',
    ROUND(bytes/POW(2,FLOOR(LOG2(bytes)/10)*10),2) ,   
    CASE (floor(log2(bytes)/10))
      WHEN 1 THEN 'KiB'  
      WHEN 2 THEN 'MiB'  
      WHEN 3 THEN 'GiB'  
      WHEN 4 THEN 'TiB'  
      ELSE 'XXX' 
    END)
  END,15,' ') ; 
Usage: SELECT byte_format(1024)

MS SQL

 
UPDATE table
   SET XmlColumn.modify('
replace value of (//xmlDetails/@product)[1]
with sql:column("otherColumn") 
')
 WHERE [condition]


SELECT XmlColumn.value('(//xmlDetails/@product)[1]', 'varchar(max)') AS product
  FROM table
 WHERE [condition]


SELECT TOP 50 CASE
                WHEN EventTypeName LIKE '%LimitChanged%' THEN
                  'Limit changed'
                WHEN EventTypeName LIKE '%LimitCreated%' THEN
                  'Limit created'
                WHEN EventTypeName LIKE '%LimitCalculated%' THEN
                  'Limit calculated'
                ELSE
                  EventTypeName
              END                                                                                        AS Event
              , [SerializedEventArgs].value('(//*[local-name()="DebtorId"]/text())[1]', 'NVARCHAR(max)') debtorId
              , [Status]
              , [CreatedAt]
              , [ExecutionStarted]
              , [ExecutionEnded]
              , [IsAsync]
              , [ParentEventSubscriberJournalId]
              , [SenderModule]
  FROM [ZUnit110].[EventSystem].[EventJournal]
 WHERE EventArgsTypeName LIKE 'Svg.Debitex.Server.Limit.Interface%'
       AND [SerializedEventArgs].exist('//DebtorId[text()=991000]') = 1
 ORDER BY CreatedAt DESC 

SELECT xmldata.query('(//vouchers)') AS Vouchers
  INTO #PSC_vouchers
  FROM ##tmp_Barbados_files_xml
 WHERE id = 1


DECLARE @Vouchers AS XML

SELECT @Vouchers = Vouchers.query('(/vouchers/voucher)')
  FROM #PSC_vouchers


SELECT Tbl.Col.value('debtorNumber[1]', 'varchar(16)') DebtorNumber
  , Tbl.Col.value('voucherNumber[1]', 'varchar(64)') VoucherNumber
  , Tbl.Col.query('.') AS Voucher
FROM @Vouchers.nodes('/voucher') Tbl(Col)

SELECT 'Toll' AS 'Knoten/@WieIstDas'
  , '123' AS Knoten
  , 'Test' AS DataSource
  , 'B2BDebtorType' AS DebtorType
  , (
    SELECT 'Toll' AS 'Knoten/@WieIstDas'
      , '123456' AS NochEinKnoten
      , 'Aha' AS Effekt
      , 'B2BDebtorType' AS DebtorType
    FOR XML PATH('DiesIstEinChildKnoten')
      , type
    )
FOR XML PATH('')
  , ROOT('DiesIstDerRootKnoten')
ergibt
<DiesIstDerRootKnoten>
  <Knoten WieIstDas="Toll">123</Knoten>
  <DataSource>Test</DataSource>
  <DebtorType>B2BDebtorType</DebtorType>
  <DiesIstEinChildKnoten>
    <Knoten WieIstDas="Toll" />
    <NochEinKnoten>123456</NochEinKnoten>
    <Effekt>Aha</Effekt>
    <DebtorType>B2BDebtorType</DebtorType>
  </DiesIstEinChildKnoten>
</DiesIstDerRootKnoten>

SELECT CURRENT_TIMESTAMP AS 'CURRENT_TIMESTAMP'
  , CONVERT(VARCHAR(16), CURRENT_TIMESTAMP, 127) TimestampAsString
  , '16' VarcharLen
–> 2016-02-01T18:16

XPath

Gegeben sei folgende Beispieldatei:

<?xml version="1.0" encoding="UTF-8"?>
<Personen>
	<Person id="1" sex="m">
		<name>Mustermann</name>
		<vorname>Max</vorname>
		<plz>12345</plz>
		<ort>Köln</ort>
	</Person>
	<Person id="2" sex="f">
		<name>Musterfrau</name>
		<vorname>Maria</vorname>
		<plz>67890</plz>
		<ort>Knuffingen</ort>
	</Person>
</Personen>

alle Vornamen //vorname1)
Name der Person mit id = 1 //Person[attribute::id='1']/name
oder //Person[@id='1']/name
Vornamen länger als drei buchstaben //vorname[string-length()>3]
Orte mit PLZ < 50000 /Personen/Person[./plz/text() < '50000']/ort
ID der letzten Person /Personen/Person[count(/Personen/Person)]/attribute::id
oder /Personen/Person[last()]/@id
Ausgabe aller Textknoten //self::text()
Ausgabe aller
Werte (name, vorname …) der ersten Person
/Personen/Person[1]/*
Ausgabe aller
Attribute (id, sex) der ersten Person
/Personen/Person[1]/@*

Links:

Groovy

Gerade für soapUI benötige ich immer wieder ein paar Groovy-Scripte.

def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )

def PartySearchRequestStep = "AgencyGatewayPerformRequest - Crefo_V2:PartySearchRequest"
def ProductRequestStep = "AgencyGatewayPerformRequest - Crefo_V2:ProductRequest"

// -- Zielordner
def filePath = "d:/soapUIResults/Crefo_V2/"
def fileName = ""
def date = new Date()
def dateFormat = new java.text.SimpleDateFormat('yyyyMMdd-HHmmss')
def shortDate = dateFormat.format(date)
def outputFolder = filePath+shortDate
def createFolder = new File(outputFolder)

/*
* extractFakeResponse:
*/
def extractFakeResponse(testStep)
{
	if (testStep.isEmpty()) return response
	
	def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
	def agencyGatewayResponseNodes = groovyUtils.getXmlHolder( testStep + "#Response" ).getDomNodes("//*:AgencyGatewayResponse/*")
	def fakeResponse = "<?xml version=\"1.0\" encoding=\"UTF-8\"?><!-- created by schroedp using soapUI -->\n"
	fakeResponse = fakeResponse.concat("<agw:responseType xmlns:agw=\"http://ws.agw.guardean.net/schema\">\n")
	for(int i = 0; i < agencyGatewayResponseNodes.size(); i++)
	{
		fakeResponse = fakeResponse.concat(groovyUtils.getXml(agencyGatewayResponseNodes[i]))
	}
	fakeResponse = fakeResponse.concat("\n</agw:responseType>")
	return fakeResponse
}

/*
 * extractOriginalResponse:
 */
def extractOriginalResponse(testStep)
{
	if (testStep.isEmpty()) return response
	
	def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )	
	originalResponse = groovyUtils.getXmlHolder( testStep + "#Response" ).getNodeValue("//additional[name = 'originalResponse'][1]/value")	
	if(originalResponse == null)
	{
	   originalResponse = groovyUtils.getXmlHolder( testStep + "#Response" ).getNodeValue("//xmlDetails[1]/content")
	}
	return originalResponse	
}

/*
 * extractResponse:
 */
 def extractResponse(testStep)
 {
 	return testRunner.testCase.testSteps[testStep].testRequest.response.contentAsString
 }


/*
 * Main ()
 */
createFolder.mkdir()  

// -- save Response
new File (outputFolder + "/PartySearchRequest_res.xml").write(extractResponse(PartySearchRequestStep), 'UTF-8')

// -- save originalResponse
new File (outputFolder + "/PartySearchRequest_orgRes.xml").write(extractOriginalResponse(PartySearchRequestStep), 'UTF-8')

// -- save FakeFile
new File (outputFolder + "/res_BITTE.ag").write(extractFakeResponse(PartySearchRequestStep), 'UTF-8')
	
// -- parse PartySearchRequest
crefoResponseXML = groovyUtils.getXmlHolder(PartySearchRequestStep + "#Response")
idCount = (crefoResponseXML.getNodeValue("count(//party)")).toInteger()
log.info "party count: " + idCount


// -- foreach Party make a ProductRequest
for ( int i = 1; i <= idCount; i++)
{
	xpath = "//party["+i+"]/identifier"
 	crefoId = crefoResponseXML.getNodeValue(xpath);
 	testRunner.testCase.setPropertyValue("crefoId", crefoId) 
	log.info "get Report " + i + " from " + idCount + " using crefoId " + crefoId
	testRunner.runTestStepByName( ProductRequestStep )
	log.info "save response"
	// -- save Response
	new File (outputFolder + "/ProductRequest_" + crefoId + "_res.xml").write(extractResponse(ProductRequestStep),'UTF-8')
	
	// -- save originalResponse
	new File (outputFolder + "/ProductRequest_" + crefoId + "_orgRes.xml").write(extractOriginalResponse(ProductRequestStep),'UTF-8')

	// -- save FakeFile
	new File (outputFolder + "/res_" + crefoId + ".ag").write(extractFakeResponse(ProductRequestStep), 'UTF-8')
}

log.info "finished"

1) wenn namespaces (Person xmlns=„http://sv.ag“) ins Spiel kommen und diese unqualifiziert sind, dann kann(!) folgendes klappen: //child::*[name()='vorname']
Driven by DokuWiki Hosted by RootPfad
© 1997-2011 macphil • E-Mail: post <at> macphil.de