问:
Postgres Check for Empty JSONB Fields
I want to be able to ignore or prevent an INSERT from occurring if there is a JSON object with keys but no values from within a Postgres function.Here is a small, contrived example:

```<code>DROP TABLE IF EXISTS mytable;
create table mytable(
a text,
b text
);

CREATE OR REPLACE FUNCTION insert_to_table(
somedata JSONB
)
RETURNS VOID AS $$
BEGIN
insert into mytable (a, b) select a,b from jsonb_populate_recordset(null::mytable, $1::jsonb);
END;
$$ LANGUAGE plpgsql;

select insert_to_table('[{"a":1,"b":2},{"a":3,"b":4}, {"a":null, "b": null}, {"a": null, "b": "some data"}]');
</code>```

This will insert 4 records, with the first row being 1,2 and the next row being 3,4. The third row is "", "", and the forth is "", some data.

In this scenario, rows 1,2, and 4 are valid. I want to ignore 3 and prevent it from being inserted.

I do not want a blank row, and my data/table will be much larger than what is listed (roughly 20 fields in the table, and 20 key/value pairs in the JSON).

Most likely I will need to loop over the array and pick out JSON object where ALL the keys are null and not just 1 or 2.

How can I do that?

答:
In Postgres you can refer to a complete row using the name of the table (alias) in the query and compare that to NULL. A record is considered NULL if all columns are null. So you can do:

```<code>create or replace function insert_to_table(somedata jsonb)
returns void as $$
begin
    insert into mytable (a, b)
    select a, b
    from jsonb_populate_recordset(null::mytable, somedata) as t
    where not (t is null);
end;
$$ language plpgsql;
</code>```

Note that where t is not null is something different then where not (t is null). This works regardless of the number of columns or their data types.

To visualize the logic. The following:

```<code>select a,b,
       not (t is null) as "not (t is null)",
       t is null as "t is null",
       t is not null as "t is not null"
from jsonb_populate_recordset(null::mytable,
       '[{"a":1,"b":2},
         {"a":3,"b":4},
         {"a":null, "b": null},
         {"a": null, "b": "some data"}]'::jsonb) as t(a,b)
</code>```

returns:

```<code>a | b         | not (t is null) | t is null | t is not null
--+-----------+-----------------+-----------+--------------
1 | 2         | true            | false     | true
3 | 4         | true            | false     | true
  |           | false           | true      | false
  | some data | true            | false     | false
</code>```

Unrelated:

The cast $1::jsonb is useless as you have declared the parameter of that type already.

Title: Postgres Check for Empty JSONB Fields

Body:
I want to be able to ignore or prevent an INSERT from occurring if there is a JSON object with keys but no values from within a Postgres function.Here is a small, contrived example:

DROP TABLE IF EXISTS mytable;
create table mytable(
a text,
b text
);

CREATE OR REPLACE FUNCTION insert_to_table(
somedata JSONB
)
RETURNS VOID AS $$
BEGIN
insert into mytable (a, b) select a,b from jsonb_populate_recordset(null::mytable, $1::jsonb);
END;
$$ LANGUAGE plpgsql;

select insert_to_table('[{"a":1,"b":2},{"a":3,"b":4}, {"a":null, "b": null}, {"a": null, "b": "some data"}]');

This will insert 4 records, with the first row being 1,2 and the next row being 3,4. The third row is "", "", and the forth is "", some data.

In this scenario, rows 1,2, and 4 are valid. I want to ignore 3 and prevent it from being inserted.

I do not want a blank row, and my data/table will be much larger than what is listed (roughly 20 fields in the table, and 20 key/value pairs in the JSON).

Most likely I will need to loop over the array and pick out JSON object where ALL the keys are null and not just 1 or 2.

How can I do that?

Body:
In Postgres you can refer to a complete row using the name of the table (alias) in the query and compare that to NULL. A record is considered NULL if all columns are null. So you can do:

create or replace function insert_to_table(somedata jsonb)
returns void as $$
begin
    insert into mytable (a, b)
    select a, b
    from jsonb_populate_recordset(null::mytable, somedata) as t
    where not (t is null);
end;
$$ language plpgsql;

Note that where t is not null is something different then where not (t is null). This works regardless of the number of columns or their data types.

To visualize the logic. The following:

select a,b,
       not (t is null) as "not (t is null)",
       t is null as "t is null",
       t is not null as "t is not null"
from jsonb_populate_recordset(null::mytable,
       '[{"a":1,"b":2},
         {"a":3,"b":4},
         {"a":null, "b": null},
         {"a": null, "b": "some data"}]'::jsonb) as t(a,b)

returns:

a | b         | not (t is null) | t is null | t is not null
--+-----------+-----------------+-----------+--------------
1 | 2         | true            | false     | true
3 | 4         | true            | false     | true
  |           | false           | true      | false
  | some data | true            | false     | false

Unrelated:

The cast $1::jsonb is useless as you have declared the parameter of that type already.


来源: stackexchange

问:
twilio Record entire incoming call with gather
I am conducting a survey using Voice with Twilio. Part of that survey is to let the user give a voice feedback so i used Record at first. That was exactly what i needed since i could get the mp3 + the transcription. Problem is : i can't set the language to French so my transcription come in french, instead it is recorded and transcribed as english even though i speak french.

So i decided to switch and use the Gather with the speech option which works quite well, the text comes back in french, but using that option, i can't get the mp3.

So i decided that i would record the entire call, and use Gather which would have solved my problem, except you only get to set the record parameter to true when you initiate the call (outgoing call). My survey will be taken by incoming call 95% of the time..

I there any way to achieve this without having to use Record and use another API to do the transcription ?

答:
Twilio developer evangelist here.

I'm afraid, in the situation you are in, where you need the mp3s and French transcription, then I can only recommend using <Record> and a third party transcription service. <Gather> with input type speech just doesn't support this kind of recording yet.

ContentLicense: CC BY-SA 3.0


问:
How to use roboto thin font in qml
I am trying to use robot thin font in QML in Linux.I have install bold,thin and light fonts on ubuntu. Other programs like openoffice shows me only roboto.
How do i get to use roboto thin or light in qml?</p>

答:
AlexB's answer is correct, and has cost me too long struggling why it didn't work.

For those wondering why their Roboto-Light font looks like Roboto-Bold, heres how to fix it:

```<code>FontLoader
{
   id: robotoLight
   source: "../fonts/Roboto/Roboto-Light.ttf"
}

Text
{
   text: "This text is Roboto-Light"
   font.family: robotoLight.name
   font.weight: Font.Light   // this is necessary or else it'll look like Roboto-Bold
}
</code>```

Title: How to use roboto thin font in qml

Body:
AlexB's answer is correct, and has cost me too long struggling why it didn't work.

For those wondering why their Roboto-Light font looks like Roboto-Bold, heres how to fix it:

FontLoader
{
   id: robotoLight
   source: "../fonts/Roboto/Roboto-Light.ttf"
}

Text
{
   text: "This text is Roboto-Light"
   font.family: robotoLight.name
   font.weight: Font.Light   // this is necessary or else it'll look like Roboto-Bold
}


问:
How do I style the td-elements in a table row upon dragging using jqueryui sortable
Using jQueryUI sortable I am able to drag and drop rows in a table.
I'd like to change the style of the row when dragging.

An option found on the internet is the following:

```<code>start: function(event, ui) {
    $(ui.item).css({
      'border': '1px solid #F95658',
      'background-color': '#bbb'
    });
  },
</code>```

This however does not let me style the borders or the background color of the cells.

The same goes for the placeholder.

Is there a way to style these?

$("table tbody").sortable({
  axis: 'y',
  placeholder: "ui-sortable-placeholder",
  revert: true,
  helper: fixHelper,
  start: function(event, ui) {
    $(ui.item).css({
      'border': '1px solid #F95658',
      'background-color': '#bbb'
    });
  },
  stop: function(event, ui) {
    $(ui.item).css({
      //'background-color': '#fff'
    });
  }
}).disableSelection();

var fixHelper = function(e, ui) {
  ui.children().each(function() {
    $(this).width($(this).width());
  });
  return ui;
};```
<pre class="snippet-code-css lang-css prettyprint-override"><code>table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 500px;
  margin: 50px 0 0 50px;
}

td,
th {
  border: 1px solid #cacaca;
  text-align: left;
  padding: 8px;
  background-color: #f5f5f5;
}

th {
  background-color: #dce0e3;
}

.ui-sortable-placeholder {
  background-color: #fff;
  outline: 0px dashed black;
  padding: 0px 0px;
}</code>```

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.17/themes/ui-lightness/jquery-ui.css" rel="stylesheet" />

    Company
    Contact
    Country

      Alfreds Futterkiste
      Maria Anders
      Germany

      Centro comercial
      Francisco Chang
      Mexico

      Ernst Handel
      Roland Mendel
      Austria

      Island Trading
      Helen Bennett
      UK

      Laughing Bacchus
      Yoshi Tannamuri
      Canada

      Magazzini Alimentari
      Giovanni Rovelli
      Italy

```

答:
put this for TD

```<code>$(ui.item).find('td').css({
  'border': '1px solid #F95658',
  'background-color': '#bbb'
});
</code>```

after this

```<code>$(ui.item).css({
  'border': '1px solid #F95658',
  'background-color': '#bbb'
});
</code>```

ContentLicense: CC BY-SA 4.0

Body:
Using jQueryUI sortable I am able to drag and drop rows in a table.
I'd like to change the style of the row when dragging.

An option found on the internet is the following:

start: function(event, ui) {
    $(ui.item).css({
      'border': '1px solid #F95658',
      'background-color': '#bbb'
    });
  },

This however does not let me style the borders or the background color of the cells.

The same goes for the placeholder.

Is there a way to style these?

$("table tbody").sortable({
  axis: 'y',
  placeholder: "ui-sortable-placeholder",
  revert: true,
  helper: fixHelper,
  start: function(event, ui) {
    $(ui.item).css({
      'border': '1px solid #F95658',
      'background-color': '#bbb'
    });
  },
  stop: function(event, ui) {
    $(ui.item).css({
      //'background-color': '#fff'
    });
  }
}).disableSelection();

var fixHelper = function(e, ui) {
  ui.children().each(function() {
    $(this).width($(this).width());
  });
  return ui;
};

table {
  font-family: arial, sans-serif;
  border-collapse: collapse;
  width: 500px;
  margin: 50px 0 0 50px;
}

td,
th {
  border: 1px solid #cacaca;
  text-align: left;
  padding: 8px;
  background-color: #f5f5f5;
}

th {
  background-color: #dce0e3;
}

.ui-sortable-placeholder {
  background-color: #fff;
  outline: 0px dashed black;
  padding: 0px 0px;
}

<link href="http://ajax.googleapis.com/ajax/libs/jqueryui/1.8.17/themes/ui-lightness/jquery-ui.css" rel="stylesheet" />

    Company
    Contact
    Country

      Alfreds Futterkiste
      Maria Anders
      Germany

      Centro comercial
      Francisco Chang
      Mexico

      Ernst Handel
      Roland Mendel
      Austria

      Island Trading
      Helen Bennett
      UK

      Laughing Bacchus
      Yoshi Tannamuri
      Canada

      Magazzini Alimentari
      Giovanni Rovelli
      Italy

Body:
put this for TD

$(ui.item).find('td').css({
  'border': '1px solid #F95658',
  'background-color': '#bbb'
});

after this

$(ui.item).css({
  'border': '1px solid #F95658',
  'background-color': '#bbb'
});


问:
Looping for specific values on a df
On one side I have a massive df:

```<code>df1

                A     B    C    ....
2005-11-01     5.3   22    6
2005-11-02     5.4   21    4
2005-11-03     5.2   17    7
   ....
</code>```

On the other hand I have a smaller df with the following structure;

```<code>df2

       date
A    2005-11-02
B    2005-11-01
C    2005-11-03
</code>```

What I am looking forward to is to add an additional column in df2 called price which loops over each index value and column value from df2 and look for the corresponding prices in df .

The desired output would be something like this:

```<code>       date        price
A    2005-11-02     5.4
B    2005-11-01      22
C    2005-11-03      7
</code>```

I tried :

```<code>prices=[]

for index,column in df2:
    prices.append(df.loc[column['date'][i],index.iloc[i]])
    i+=1
return prices
</code>```

However returns this undesired output.

```<code>ValueError: too many values to unpack (expected 2)
</code>```

Could anyone tell me how could I loop through df based on the index and column from df2

答:
By using lookup

```<code>df.lookup(df2.date,df2.index)
Out[1003]: array([  5.4,  22. ,   7. ])
</code>```

After assign it back

```<code>df2['Value']=df.lookup(df2.date,df2.index)
df2
Out[1005]:
         date  Value
A  2005-11-02    5.4
B  2005-11-01   22.0
C  2005-11-03    7.0
</code>```

Title: Looping for specific values on a df

Body:
On one side I have a massive df:

df1

                A     B    C    ....
2005-11-01     5.3   22    6
2005-11-02     5.4   21    4
2005-11-03     5.2   17    7
   ....

On the other hand I have a smaller df with the following structure;

df2

       date
A    2005-11-02
B    2005-11-01
C    2005-11-03

What I am looking forward to is to add an additional column in df2 called price which loops over each index value and column value from df2 and look for the corresponding prices in df .

The desired output would be something like this:

       date        price
A    2005-11-02     5.4
B    2005-11-01      22
C    2005-11-03      7

I tried :

prices=[]

for index,column in df2:
    prices.append(df.loc[column['date'][i],index.iloc[i]])
    i+=1
return prices

However returns this undesired output.

ValueError: too many values to unpack (expected 2)

Could anyone tell me how could I loop through df based on the index and column from df2

Body:
By using lookup

df.lookup(df2.date,df2.index)
Out[1003]: array([  5.4,  22. ,   7. ])

After assign it back

df2['Value']=df.lookup(df2.date,df2.index)
df2
Out[1005]:
         date  Value
A  2005-11-02    5.4
B  2005-11-01   22.0
C  2005-11-03    7.0


问:
How to Right-align flex item?
Is there a more flexbox-ish way to right-align "Contact" than to use position: absolute?

.main {
  display: flex;
}

.a,
.b,
.c {
  background: #efefef;
  border: 1px solid #999;
}

.b {
  flex: 1;
  text-align: center;
}

.c {
  position: absolute;
  right: 0;
}```
<pre class="snippet-code-html lang-html prettyprint-override"><code><h2>With title</h2>
<div class="main">
  <div class="a">Home</div>
  <div class="b">Some title centered</div>
  <div class="c">Contact</div>
</div>

<h2>Without title</h2>
<div class="main">
  <div class="a">Home</div>
  <!--<div class="b">Some title centered</div>-->
  <div class="c">Contact</div>
</div></code>```

http://jsfiddle.net/vqDK9/

答:
As easy as</p>

```<code>.main {
    display: flex;
    flex-direction:row-reverse;
}
</code>```

Title: How to Right-align flex item?

Body:
Is there a more flexbox-ish way to right-align "Contact" than to use position: absolute?

.main {
  display: flex;
}

.a,
.b,
.c {
  background: #efefef;
  border: 1px solid #999;
}

.b {
  flex: 1;
  text-align: center;
}

.c {
  position: absolute;
  right: 0;
}

With title

Home

Some title centered

Contact

Without title

Home

Contact

http://jsfiddle.net/vqDK9/

Body:
As easy as

.main {
    display: flex;
    flex-direction:row-reverse;
}


问:
"The package appears to be corrupt" while installing APK on Galaxy S7
I made an Android project on Android Studio. When I ran it on test Android device which was plugged to my system using a USB cable, it ran successfully.

However, then I generated the APK and uploaded in Diawi website. When I tried to install this APK, I got following error:

App not installed.

The package appears to be corrupt.

APK file however is not corrupt at all since when I installed it manually after copying it from build folder, it was successfully installed on the Galaxy S7.

How to fix this issue?

答: You could try avoiding put the names: com.example, com.android, etcc in your package, then Build>Rebuild and/or BuildGenerate signed APK and follow the steps. I hope help you!</p>


问:
Conditioning on strings with special characters
I've run into an unusual situation when I have an apostrophe in my string and need to do a "run keyword if" conditional. Below is my code:

```<code>*** Variables ***
${myVar} =  Joe's test

*** Test Cases ***
Testing a true IF statement
    Run Keyword If  '${myVar}' == 'Joe's test'  Keyword 1

*** Keywords ***
Keyword 1
    Log to Console  VALUE: ${myVar}
</code>```

I get the following error:

```<code>Evaluating expression 'Joe's test == 'Joe's test'' failed: SyntaxError: invalid syntax (<string>, line 1).
</code>```

I tried using a switch like \' to handle the apostrophe in the string but still no luck. Any help appreciated!

答:
Use double quotes:

```<code>run keyword if  "${myVar}" == "Joe's test"  keyword 1
</code>```

If there's a possibility that ${myVar} contains either a single quote or a double quote, you can use triple quotes:

```<code>run keyword if  '''${myVar}''' == "Joe's test"  keyword 1
</code>```

Body:
I've run into an unusual situation when I have an apostrophe in my string and need to do a "run keyword if" conditional. Below is my code:

*** Variables ***
${myVar} =  Joe's test

*** Test Cases ***
Testing a true IF statement
    Run Keyword If  '${myVar}' == 'Joe's test'  Keyword 1

*** Keywords ***
Keyword 1
    Log to Console  VALUE: ${myVar}

I get the following error:

Evaluating expression 'Joe's test == 'Joe's test'' failed: SyntaxError: invalid syntax (<string>, line 1).

I tried using a switch like \' to handle the apostrophe in the string but still no luck. Any help appreciated!

Body:
Use double quotes:

run keyword if  "${myVar}" == "Joe's test"  keyword 1

If there's a possibility that ${myVar} contains either a single quote or a double quote, you can use triple quotes:

run keyword if  '''${myVar}''' == "Joe's test"  keyword 1


问:
How can I get the data from a .pkl file using Windows OS
I have a .pkl file which loads perfectly in my MAC OS but it wont load in a windows machine. I am using python 3 on anaconda.
This is my code:

data=pickle.load(open("ydata1.pkl",'rb'))

Error: UnicodeDecodeError: 'ascii' codec can't decode byte 0xc3 in position 2295: ordinal not in range(128)

So I tried this:

data=pickle.load(open("ydata1.pkl",'r'))

But I get an error saying :  a bytes-like object is required, not 'str'

Can anyone please tell me where I am going wrong?

答:
Use open() with mode rb:

```<code>import pickle

with open('ydata1.pkl', 'rb') as p_f:
    data = pickle.load(p_f)
</code>```

Taken from documentation: https://docs.python.org/2/tutorial/inputoutput.html#reading-and-writing-files

On Windows, 'b' appended to the mode opens the file in binary mode, so
  there are also modes like 'rb', 'wb', and 'r+b'. Python on Windows
  makes a distinction between text and binary files; the end-of-line
  characters in text files are automatically altered slightly when data
  is read or written. This behind-the-scenes modification to file data
  is fine for ASCII text files, but it’ll corrupt binary data like that
  in JPEG or EXE files. Be very careful to use binary mode when reading
  and writing such files. On Unix, it doesn’t hurt to append a 'b' to
  the mode, so you can use it platform-independently for all binary
  files.

Also make sure you are not having Python 2 / Python 3 pickle compatibility issues: Pickle incompatibility of numpy arrays between Python 2 and 3

Body:
Use open() with mode rb:

import pickle

with open('ydata1.pkl', 'rb') as p_f:
    data = pickle.load(p_f)

Taken from documentation: https://docs.python.org/2/tutorial/inputoutput.html#reading-and-writing-files

On Windows, 'b' appended to the mode opens the file in binary mode, so
  there are also modes like 'rb', 'wb', and 'r+b'. Python on Windows
  makes a distinction between text and binary files; the end-of-line
  characters in text files are automatically altered slightly when data
  is read or written. This behind-the-scenes modification to file data
  is fine for ASCII text files, but it’ll corrupt binary data like that
  in JPEG or EXE files. Be very careful to use binary mode when reading
  and writing such files. On Unix, it doesn’t hurt to append a 'b' to
  the mode, so you can use it platform-independently for all binary
  files.

Also make sure you are not having Python 2 / Python 3 pickle compatibility issues: Pickle incompatibility of numpy arrays between Python 2 and 3


问:
Angularjs modal is updating my parent scope before the results sent back
I'm launching a modal from my web page and i'm making some changes to an array that i'm passing from my parent, but before I send the updated results back after closing the modal my parent scope object is getting updated. If the user changed the mind not to update and then cancel the modal I don't want those changes to be seen on my parent controller

Parent Controller Code:

const modalInstance = $modal.open({
                templateUrl: '/app/modal.html',
                controller: 'modalController',
                controllerAs: 'modal',
                resolve: {

                    mappedData: () => parentCntrl.groupData
                }
            });

```<code>        modalInstance.result.then(result => {
            if (result) {
                parentCntrl.groupData = result;
            }
        });
</code>```

Child Controller Code:

modal.ok = () => {
   $modalInstance.close(modal.mappedData);
};

modal.close = () => {
  $modalInstance.close();
};

答:
Because the data you are passing down is a non primitive type, so not an number or string for example, this will be copied by reference in memory.

You should make a copy of the data and  use the copied version in the modal and when the modal is saved use that copied version to merge any changes back to the original object in the parent controller

A very similar question asked here Using AngularJS "copy()" to avoid reference issues and how angular.copy() can be used to avoid reference issues

Body:
I'm launching a modal from my web page and i'm making some changes to an array that i'm passing from my parent, but before I send the updated results back after closing the modal my parent scope object is getting updated. If the user changed the mind not to update and then cancel the modal I don't want those changes to be seen on my parent controller

Parent Controller Code:

const modalInstance = $modal.open({
                templateUrl: '/app/modal.html',
                controller: 'modalController',
                controllerAs: 'modal',
                resolve: {

                    mappedData: () => parentCntrl.groupData
                }
            });

        modalInstance.result.then(result => {
            if (result) {
                parentCntrl.groupData = result;
            }
        });

Child Controller Code:

modal.ok = () => {
   $modalInstance.close(modal.mappedData);
};

modal.close = () => {
  $modalInstance.close();
};


问:
Is it possible to pass data between two JQuery plugin?
I have two jquery plugin of my own and the elements created by the two plugin had its own z-index value.E.g:

```<code>// auto increase the z-index value
// auto increase the z-index to the maximum of their own when I drag it.

element1 created by plugin1 and has the z-index:1;
element2 created by plugin1 and has the z-index:2;
....

// auto increase the z-index value
// auto increase the z-index to the maximum of their own when I drag it.

element2 created by plugin2 and has the z-index:1;
element2 created by plugin2 and has the z-index:2;
.....
</code>```

and both of them can be draggable,so when I click one of them I want its z-index value to be the maximum of all the elements I created.But now the z-index were separated!

I am using JQuery UI draggable plugin but don't like the stack option !

Thank you very much!!

答:
Try using name spaces

for eg in plugin 1
you can define

```<code>var Plugin1 = {}; //data abuot the plugin
Plugin1.element1 = {}; //data about the element
Plugin1.element1.zindex = 1000; //data about the element
</code>```

This can be retrieved in any part of the js
but make sure its defined before document.ready

ContentLicense: CC BY-SA 2.5

Body:
I have two jquery plugin of my own and the elements created by the two plugin had its own z-index value.E.g:

// auto increase the z-index value
// auto increase the z-index to the maximum of their own when I drag it.

element1 created by plugin1 and has the z-index:1;
element2 created by plugin1 and has the z-index:2;
....

// auto increase the z-index value
// auto increase the z-index to the maximum of their own when I drag it.

element2 created by plugin2 and has the z-index:1;
element2 created by plugin2 and has the z-index:2;
.....

and both of them can be draggable,so when I click one of them I want its z-index value to be the maximum of all the elements I created.But now the z-index were separated!

I am using JQuery UI draggable plugin but don't like the stack option !

Thank you very much!!

Body:
Try using name spaces

for eg in plugin 1
you can define

var Plugin1 = {}; //data abuot the plugin
Plugin1.element1 = {}; //data about the element
Plugin1.element1.zindex = 1000; //data about the element

This can be retrieved in any part of the js
but make sure its defined before document.ready


问:
Make a table in R that resembles facet_grid in structure
I would like to make a "nested" sort of table in R that mirrors the formatting of a plot I can make with ggplot using facet_wrap.

Here are some data and the code:

```<code>tabledata = structure(list(row = c(1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L,1L, 2L, 1L, 2L, 1L, 2L),
                       col = c(1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L),
                       grp1 = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L),
                                        .Label = c("a", "b"), class = "factor"),
                       grp2 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L),
                                        .Label = c("g", "h"), class = "factor"),
                       value = c(9L, 9L, 14L, 8L, 10L, 9L, 8L, 15L, 2L, 1L, 3L, 4L, 1L, 5L, 2L, 4L)),
                  .Names = c("row", "col", "grp1", "grp2", "value"), class = "data.frame",
                  row.names = c(NA, -16L))

ggplot(tabledata, aes(grp2, value, shape = grp1)) + geom_jitter() + facet_grid(row ~ col)
</code>```

Which produce this plot:

Here is the table I would like to make (which can easily be done with a pivot table, but obviously that is not ideal):

答:
A nested table can be made using the tabular() function in the tables package using the following code.

```<code>tabular(
    (Heading()*Factor(row)*Heading()*grp1)~
    (Heading()*Factor(col)*Heading()*grp2)*Heading()*value*Heading()*identity,
data = tabledata)
</code>```

The table can then be saved as a .csv file using write.csv.tabular().

Body:
I would like to make a "nested" sort of table in R that mirrors the formatting of a plot I can make with ggplot using facet_wrap.

Here are some data and the code:

tabledata = structure(list(row = c(1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L, 1L, 2L,1L, 2L, 1L, 2L, 1L, 2L),
                       col = c(1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L, 1L, 1L, 2L, 2L),
                       grp1 = structure(c(1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L),
                                        .Label = c("a", "b"), class = "factor"),
                       grp2 = structure(c(1L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L),
                                        .Label = c("g", "h"), class = "factor"),
                       value = c(9L, 9L, 14L, 8L, 10L, 9L, 8L, 15L, 2L, 1L, 3L, 4L, 1L, 5L, 2L, 4L)),
                  .Names = c("row", "col", "grp1", "grp2", "value"), class = "data.frame",
                  row.names = c(NA, -16L))

ggplot(tabledata, aes(grp2, value, shape = grp1)) + geom_jitter() + facet_grid(row ~ col)

Which produce this plot:

Here is the table I would like to make (which can easily be done with a pivot table, but obviously that is not ideal):

Body:
A nested table can be made using the tabular() function in the tables package using the following code.

tabular(
    (Heading()*Factor(row)*Heading()*grp1)~
    (Heading()*Factor(col)*Heading()*grp2)*Heading()*value*Heading()*identity,
data = tabledata)

The table can then be saved as a .csv file using write.csv.tabular().


问:
How to find the sum of an array of numbers
Given an array [1, 2, 3, 4], how can I find the sum of its elements? (In this case, the sum would be 10.)

I thought $.each might be useful, but I'm not sure how to implement it.

答:
For really large numbers cycling or reducing could be process intensive. What about using Gauss?

```<code>sum = (n * (n+1))/2;
</code>```

From mathcentral.

Body:
For really large numbers cycling or reducing could be process intensive. What about using Gauss?

sum = (n * (n+1))/2;

From mathcentral.


问:
How to insert data using peewee?
I have described a model of peewee:

```<code>class Products(peewee.Model):
    article = peewee.CharField(primary_key=True)
    name = peewee.CharField()
    price = peewee.FloatField()
    price_distributor = peewee.FloatField()
    price_discount = peewee.FloatField()

    class Meta:
        database = get_db_connection()
</code>```

Then tried to insert data like:

```<code>row = {
                'article': row[0].value,
                'name': row[1].value,
                'price': row[2].value,
                'price_distributor': row[3].value,
                'price_discount': row[4].value
            }

# Insert
Products.insert(row)
</code>```

It does not give any errors, just database is empty

答: You need to call .execute() afterwards. Same for update & delete.</p>

Title: How to insert data using peewee?

Body:
I have described a model of peewee:

class Products(peewee.Model):
    article = peewee.CharField(primary_key=True)
    name = peewee.CharField()
    price = peewee.FloatField()
    price_distributor = peewee.FloatField()
    price_discount = peewee.FloatField()

    class Meta:
        database = get_db_connection()

Then tried to insert data like:

row = {
                'article': row[0].value,
                'name': row[1].value,
                'price': row[2].value,
                'price_distributor': row[3].value,
                'price_discount': row[4].value
            }

# Insert
Products.insert(row)

It does not give any errors, just database is empty


问:
How to change the position of an array element?
I have a question on how I can change the index of a array element, so that it doesn't come at the 7. position but at position 2 instead...

Is there a function to handle this?

答:
Nothing is simpler:</p>

```<code>array.insert(2, array.delete_at(7))
</code>```

Body:
Nothing is simpler:

array.insert(2, array.delete_at(7))


问:
How to write numeric values to a file and read the same values in another thread in Python?
I want to write 4 numeric values to a file using one thread and read the same values in an another thread. All of this should of course be simultaneous. The second thread will only read values not modify them. Can it be done? </p>

答:
First of all, if you want to transfer data among threads which belong to the same process - you don't need to do it using files as a communication point. The easiest way is to use queues which are thread safe python mechanisms to send data from one thread to another using a FIFO style.

```<code>#!/usr/bin/python3
from threading import Thread
from queue import Queue
from time import sleep

q = Queue()

def run_func_in_diff_thread(func, *args):
    t = Thread(target=func, args=args)
    t.setDaemon = True
    t.start()

def writer(data_set):
    global q  # queue must be shared/common for our threads
    print("Writer thread started")
    for elem in data_set:
        q.put(elem)  # put current elem into queue
        print("%s > %d" % ("writer", elem))
        sleep(1)
    q.put(-1)  # something like last element
    print("Writer thread finished since last element has been put")

def reader():
    global q  # queue must be shared/common for our threads
    print("Reader thread started")
    while True:
        elem = q.get()  # remove and return an item from the queue
        print("%s > %d" % ("reader", elem))
        sleep(0.1)
        if elem == -1:
            break
    print("Reader thread finished since last element has been received")

data = [x for x in range(5)]
run_func_in_diff_thread(writer, data)
run_func_in_diff_thread(reader)
</code>```

You will get the following output:

```<code>Writer thread started
writer > 0
Reader thread started
reader > 0
writer > 1
reader > 1
writer > 2
reader > 2
writer > 3
reader > 3
writer > 4
reader > 4
Writer thread finished since last element has been put
reader > -1
Reader thread finished since last element has been received
</code>```

This is very simple, isnt' it?

Ok, if you really need to use a file to send data between your threads, I can suggest the following:

```<code>#!/usr/bin/python3
from threading import Thread, Lock
from time import sleep

lock = Lock()  # lock to synchronize your threads

def run_func_in_diff_thread(func, *args):
    t = Thread(target=func, args=args)
    t.setDaemon = True
    t.start()

def read_or_write(path, type='r', data=None):
    if type == 'r':
        with open(path, type) as f:
            elem = f.readlines()[-1]  # read last element
            print("Read data from file < %d" % int(elem))
            if int(elem) == -1:
                print("Received last element. Stop reading")
                return False
            return True

    else:
        with open(path, type) as f:
            print("Write data to file > %d" % data)
            f.write("%s\n" % str(data))
            return True

def func_runner(func, file_path, type, data_set=None):
    if type == "a":
        # writer
        for data in data_set:
            lock.acquire()
            read_or_write(file_path, type, data)
            lock.release()
            sleep(1)
    else:
        # reader
        while True:
            lock.acquire()
            res = func(file_path, type)
            lock.release()
            if not res:
                break
            sleep(1)

data_set = [x for x in range(5)]
data_set.append(-1)  # something like last element
file_path = "file.txt"
run_func_in_diff_thread(func_runner, read_or_write, file_path, "a", data_set)
run_func_in_diff_thread(func_runner, read_or_write, file_path, "r")
</code>```

And you'll get the following output:

```<code>Write data to file > 0
Read data from file < 0
Read data from file < 0
Write data to file > 1
Read data from file < 1
Write data to file > 2
Read data from file < 2
Write data to file > 3
Read data from file < 3
Write data to file > 4
Read data from file < 4
Write data to file > -1
Read data from file < -1
Received last element. Stop reading
</code>```

In this case you need to use Lock - built-in mechanism to synchronize threads to avoid data corruption.

As I said, please consider using Queue rather than using a file for thread syncing.

Body:
First of all, if you want to transfer data among threads which belong to the same process - you don't need to do it using files as a communication point. The easiest way is to use queues which are thread safe python mechanisms to send data from one thread to another using a FIFO style.

#!/usr/bin/python3
from threading import Thread
from queue import Queue
from time import sleep

q = Queue()

def run_func_in_diff_thread(func, *args):
    t = Thread(target=func, args=args)
    t.setDaemon = True
    t.start()

def writer(data_set):
    global q  # queue must be shared/common for our threads
    print("Writer thread started")
    for elem in data_set:
        q.put(elem)  # put current elem into queue
        print("%s > %d" % ("writer", elem))
        sleep(1)
    q.put(-1)  # something like last element
    print("Writer thread finished since last element has been put")

def reader():
    global q  # queue must be shared/common for our threads
    print("Reader thread started")
    while True:
        elem = q.get()  # remove and return an item from the queue
        print("%s > %d" % ("reader", elem))
        sleep(0.1)
        if elem == -1:
            break
    print("Reader thread finished since last element has been received")

data = [x for x in range(5)]
run_func_in_diff_thread(writer, data)
run_func_in_diff_thread(reader)

You will get the following output:

Writer thread started
writer > 0
Reader thread started
reader > 0
writer > 1
reader > 1
writer > 2
reader > 2
writer > 3
reader > 3
writer > 4
reader > 4
Writer thread finished since last element has been put
reader > -1
Reader thread finished since last element has been received

This is very simple, isnt' it?

Ok, if you really need to use a file to send data between your threads, I can suggest the following:

#!/usr/bin/python3
from threading import Thread, Lock
from time import sleep

lock = Lock()  # lock to synchronize your threads

def run_func_in_diff_thread(func, *args):
    t = Thread(target=func, args=args)
    t.setDaemon = True
    t.start()

def read_or_write(path, type='r', data=None):
    if type == 'r':
        with open(path, type) as f:
            elem = f.readlines()[-1]  # read last element
            print("Read data from file < %d" % int(elem))
            if int(elem) == -1:
                print("Received last element. Stop reading")
                return False
            return True

    else:
        with open(path, type) as f:
            print("Write data to file > %d" % data)
            f.write("%s\n" % str(data))
            return True

def func_runner(func, file_path, type, data_set=None):
    if type == "a":
        # writer
        for data in data_set:
            lock.acquire()
            read_or_write(file_path, type, data)
            lock.release()
            sleep(1)
    else:
        # reader
        while True:
            lock.acquire()
            res = func(file_path, type)
            lock.release()
            if not res:
                break
            sleep(1)

data_set = [x for x in range(5)]
data_set.append(-1)  # something like last element
file_path = "file.txt"
run_func_in_diff_thread(func_runner, read_or_write, file_path, "a", data_set)
run_func_in_diff_thread(func_runner, read_or_write, file_path, "r")

And you'll get the following output:

Write data to file > 0
Read data from file < 0
Read data from file < 0
Write data to file > 1
Read data from file < 1
Write data to file > 2
Read data from file < 2
Write data to file > 3
Read data from file < 3
Write data to file > 4
Read data from file < 4
Write data to file > -1
Read data from file < -1
Received last element. Stop reading

In this case you need to use Lock - built-in mechanism to synchronize threads to avoid data corruption.

As I said, please consider using Queue rather than using a file for thread syncing.


问:
check isbn 10 number java
I should make a programm to control the number of an isbn 10 number. Therefore I'm not allowed to use arrays andd the input of the number has to be a char. The In method is similar to java scanner.

```<code>public class ISBN {

    public static void main(String[] args) {
    System.out.println("ISBN - Pruefung");
    System.out.println("=================");
    System.out.print("ISBN-Nummer: ");
    char isbn = In.read();

    int check = 0;
    int d=0;

    for (d=0; d<10; d++) {

        if ('0' <= isbn && isbn <= '9' ) {
            check = (int) ((isbn-48)*d)+check;
            if(d ==9) {
                int lastDigit = check%11;
                if(lastDigit ==10) {
                    System.out.println("x");
                }else {
                System.out.println(lastDigit);
                }
            }else {
                System.out.print(isbn);
            }

        }else {
            System.out.println(isbn + "Falsche Eingabe");
            System.exit(0);
        }
        isbn = In.read();
    }
    if (d == 10 && check%11 ==0) {
        System.out.println("wahr");
    }else {
        System.out.println("falsch");
    }

    }

}
</code>```

I googled some isbn 10 numbers but my programs says they are wrong (example 2123456802). Now my question where is my mistake and/or understood I the function of the last number wrong?

答:
the sum of all the ten digits, each multiplied by its (integer) weight, descending from 10 to 1, is a multiple of 11.

So you just need to sum that digit value time the weight :

```<code>int check = 0;
for(int weight = 10; weight > 0; weigth--){
    char c = In.read(); //get the next character
    int i;
    if( c == 'x' || c == 'X' ){
        i = 10;
    } else {
        if(! Character.isDigit(c)) //Because, just in case...
            throw new IllegalArgumentException("Not a numeric value");

        i = Character.getNumericValue( c );
    }
    check += i * weight;
}
</code>```

Just need to check if it is a multiple of 11

```<code>if ( check % 11 == 0 )
    System.out.println( "VALID" );
else
    System.out.println( "INVALID" );
</code>```

Title: check isbn 10 number java

Body:
I should make a programm to control the number of an isbn 10 number. Therefore I'm not allowed to use arrays andd the input of the number has to be a char. The In method is similar to java scanner.

public class ISBN {

    public static void main(String[] args) {
    System.out.println("ISBN - Pruefung");
    System.out.println("=================");
    System.out.print("ISBN-Nummer: ");
    char isbn = In.read();

    int check = 0;
    int d=0;

    for (d=0; d<10; d++) {

        if ('0' <= isbn && isbn <= '9' ) {
            check = (int) ((isbn-48)*d)+check;
            if(d ==9) {
                int lastDigit = check%11;
                if(lastDigit ==10) {
                    System.out.println("x");
                }else {
                System.out.println(lastDigit);
                }
            }else {
                System.out.print(isbn);
            }

        }else {
            System.out.println(isbn + "Falsche Eingabe");
            System.exit(0);
        }
        isbn = In.read();
    }
    if (d == 10 && check%11 ==0) {
        System.out.println("wahr");
    }else {
        System.out.println("falsch");
    }

    }

}

I googled some isbn 10 numbers but my programs says they are wrong (example 2123456802). Now my question where is my mistake and/or understood I the function of the last number wrong?

Body:
the sum of all the ten digits, each multiplied by its (integer) weight, descending from 10 to 1, is a multiple of 11.

So you just need to sum that digit value time the weight :

int check = 0;
for(int weight = 10; weight > 0; weigth--){
    char c = In.read(); //get the next character
    int i;
    if( c == 'x' || c == 'X' ){
        i = 10;
    } else {
        if(! Character.isDigit(c)) //Because, just in case...
            throw new IllegalArgumentException("Not a numeric value");

        i = Character.getNumericValue( c );
    }
    check += i * weight;
}

Just need to check if it is a multiple of 11

if ( check % 11 == 0 )
    System.out.println( "VALID" );
else
    System.out.println( "INVALID" );


问:
Add custom content for a specific product on WooCommerce single product pages
In Woocommerce, How can I add a custom content for a specific product on single product pages?

Here is an explicit screen shot:

答:
As your screenshot is not so clear on where you want this custom content you have 2 options:

1) Under the product price

With this custom function hooked in woocommerce_before_single_product_summary action hook you can add some custom content to a specific product ID (to be defined in the function) this way:

```<code>add_action( 'woocommerce_single_product_summary', 'add_custom_content_for_specific_product', 15 );
function add_custom_content_for_specific_product() {
    global $product;

    // Limit to a specific product ID only (Set your product ID below )
    if( $product->get_id() != 37 ) return;

    // The content start below (with translatables texts)
    ?>
        <div class="custom-content product-id-<?php echo $product->get_id(); ?>">
            <h3><?php _e("My custom content title", "woocommerce"); ?></h3>
            <p><?php _e("This is my custom content text, this is my custom content text, this is my custom content text…", "woocommerce"); ?></p>
        </div>
    <?php
    // End of content
}
</code>```

2) Under the product image:

With this custom function hooked in woocommerce_before_single_product_summary action hook you can add some custom content to a specific product ID (to be defined in the function) this way:

```<code>add_action( 'woocommerce_before_single_product_summary', 'add_custom_content_for_specific_product', 25 );
function add_custom_content_for_specific_product() {
    global $product;

    // Limit to a specific product ID only (Set your product ID below )
    if( $product->get_id() != 37 ) return;

    // The content start below (with translatables texts)
    ?>
        <div class="custom-content product-id-<?php echo $product->get_id(); ?>">
            <h3><?php _e("My custom content title", "woocommerce"); ?></h3>
            <p><?php _e("This is my custom content text, this is my custom content text, this is my custom content text…", "woocommerce"); ?></p>
        </div>
    <?php
    // End of content
}
</code>```

If you want to remove the product short description you can add into the function just after the if statement:

```<code>remove_action( 'woocommerce_single_product_summary', 'woocommerce_template_single_excerpt', 20 );
</code>```

Code goes in functions.php file of your active child theme (or theme) or also in any plugin file.

Tested and works…

Body:
As your screenshot is not so clear on where you want this custom content you have 2 options:

1) Under the product price

With this custom function hooked in woocommerce_before_single_product_summary action hook you can add some custom content to a specific product ID (to be defined in the function) this way:

add_action( 'woocommerce_single_product_summary', 'add_custom_content_for_specific_product', 15 );
function add_custom_content_for_specific_product() {
    global $product;

    // Limit to a specific product ID only (Set your product ID below )
    if( $product->get_id() != 37 ) return;

    // The content start below (with translatables texts)
    ?>
        <div class="custom-content product-id-<?php echo $product->get_id(); ?>">
            <h3><?php _e("My custom content title", "woocommerce"); ?></h3>
            <p><?php _e("This is my custom content text, this is my custom content text, this is my custom content text…", "woocommerce"); ?></p>
        </div>
    <?php
    // End of content
}

2) Under the product image:

With this custom function hooked in woocommerce_before_single_product_summary action hook you can add some custom content to a specific product ID (to be defined in the function) this way:

add_action( 'woocommerce_before_single_product_summary', 'add_custom_content_for_specific_product', 25 );
function add_custom_content_for_specific_product() {
    global $product;

    // Limit to a specific product ID only (Set your product ID below )
    if( $product->get_id() != 37 ) return;

    // The content start below (with translatables texts)
    ?>
        <div class="custom-content product-id-<?php echo $product->get_id(); ?>">
            <h3><?php _e("My custom content title", "woocommerce"); ?></h3>
            <p><?php _e("This is my custom content text, this is my custom content text, this is my custom content text…", "woocommerce"); ?></p>
        </div>
    <?php
    // End of content
}

If you want to remove the product short description you can add into the function just after the if statement:

remove_action( 'woocommerce_single_product_summary', 'woocommerce_template_single_excerpt', 20 );

Code goes in functions.php file of your active child theme (or theme) or also in any plugin file.

Tested and works…


问:
How to convert DateTimeOffset to DateTime in Linq
I have this query which returns incorrect results:

```<code> return contextProvider
                      .Context
                      .OrderAccounts
                      .Where(orderAccount => listCSRId.Contains(orderAccount.OrderCust.UserId) &&
                                    orderAccount.OrderCust.SubmittedDate >= System.Data.Entity.DbFunctions.TruncateTime(startDate)
                                    && orderAccount.OrderCust.SubmittedDate <= startDate
                                    && orderAccount.OrderCust.OrderStatusId == (int)OrderStatus.Submitted)
                       .Select(x => new Order
                       {
                         UserId = x.OrderCust.UserId,
                         FacilityCode = x.FacilityCode,
                         GreenFlag = x.GreenFlag ?? false,
                         Payable = x.Payable,
                         OrderCustId = x.OrderCustId
                       })
                      .ToList();
</code>```

The reason that the results are incorrect is that the SubmittedDate is a DateTimeOffSet in Sql Server.  So when the local DateTime of 11 PM on 2017-11-15 is stored, it is stored as 04:00 2017-11-16 due to the Offset between local time, and GMT.

When the query runs, the startDate is just a date without a time.  So what is needed is to convert the SubmittedDate in the table to a Local Date.  Then the query would be correct.

How do I do that?

答:
The issue turned out to be that the 'startDate' parameter was a DateTime, instead of a DateTimeOffset.

When this parameter was changed to a DateTimeOffset, then the queries started working properly.

In other words, a DateTimeOffset with a value of 2017-11-15 00:00 with an offset of -5:00 is converted to a UTC of 2017-11-15 05:00 (with no Offset). I think EntityFramework/Sql Server is smart enough to compare DateTimeOffsets correctly, it doing the conversion to UTC (with no offset) somewhere along the line)

So now any submitted date greater than 2017-11-15 05:00 is returned which is what I wanted (the current day records - local time being 2017-11-15 00:00).

Time is such a slippery subject, hopefully I have explained this correctly.

The takeway is: Compare Sql Server DateTimeOffsets  with a .net DateTimeOffset, not a DateTime.

Body:
I have this query which returns incorrect results:

 return contextProvider
                      .Context
                      .OrderAccounts
                      .Where(orderAccount => listCSRId.Contains(orderAccount.OrderCust.UserId) &&
                                    orderAccount.OrderCust.SubmittedDate >= System.Data.Entity.DbFunctions.TruncateTime(startDate)
                                    && orderAccount.OrderCust.SubmittedDate <= startDate
                                    && orderAccount.OrderCust.OrderStatusId == (int)OrderStatus.Submitted)
                       .Select(x => new Order
                       {
                         UserId = x.OrderCust.UserId,
                         FacilityCode = x.FacilityCode,
                         GreenFlag = x.GreenFlag ?? false,
                         Payable = x.Payable,
                         OrderCustId = x.OrderCustId
                       })
                      .ToList();

The reason that the results are incorrect is that the SubmittedDate is a DateTimeOffSet in Sql Server.  So when the local DateTime of 11 PM on 2017-11-15 is stored, it is stored as 04:00 2017-11-16 due to the Offset between local time, and GMT.

When the query runs, the startDate is just a date without a time.  So what is needed is to convert the SubmittedDate in the table to a Local Date.  Then the query would be correct.

How do I do that?


问:
AddToAny not working in FireFox
I'm trying to use AddToAny to get social media sharing on my website, but it looks like FireFox is blocking it, due to tracking prevention.

Console Message:

```<code>The resource at “https://static.addtoany.com/menu/page.js” was blocked because tracking protection is enabled.
</code>```

Is there a way to prevent tracking? My code is very basic:

```<code><!-- AddToAny BEGIN -->
  <div class="a2a_kit a2a_kit_size_32 a2a_default_style" data-a2a-icon-color="transparent,#8d8d8d">
    <a class="a2a_button_facebook mar-r-5"></a>
    <a class="a2a_button_twitter mar-r-5"></a>
    <a class="a2a_button_email mar-r-5"></a>
    <a class="a2a_dd" href="https://www.addtoany.com/share"></a>
  </div>
  <script async src="https://static.addtoany.com/menu/page.js"></script>
  <!-- AddToAny END -->
</code>```

答: I figured it out. It was because I was using the private mode in the FireFox browser!</p>

Title: AddToAny not working in FireFox

Body:
I'm trying to use AddToAny to get social media sharing on my website, but it looks like FireFox is blocking it, due to tracking prevention.

Console Message:

The resource at “https://static.addtoany.com/menu/page.js” was blocked because tracking protection is enabled.

Is there a way to prevent tracking? My code is very basic:


问:
How to set the environment variable in tox?
I have tox 2.9.1

```<code>$ tox --version
2.9.1 imported from /Library/Python/2.7/site-packages/tox/__init__.pyc
registered plugins:
    tox-pyenv-1.1.0 at /Library/Python/2.7/site-packages/tox_pyenv.pyc
</code>```

File structure and content are as below

```<code>$ tree .
.
├── setup.py
├── test_env.py
└── tox.ini

0 directories, 3 files
</code>```

setup.py

```<code>$ cat setup.py
from setuptools import setup

setup(name="Tox Testing")
</code>```

tox.ini

```<code>$ cat tox.ini
[tox]
envlist = py35
setenv =
    XYZ = 123

[testenv]
deps=pytest
commands=py.test
</code>```

test_env.py

```<code>$ cat test_env.py
import os

def test_env():
    assert os.getenv('XYZ') == 123
</code>```

When I run the tox command, my test is failing.

```<code>$ tox -v
using tox.ini: /private/tmp/testing/tox.ini
using tox-2.9.1 from /Library/Python/2.7/site-packages/tox/__init__.pyc
GLOB sdist-make: /private/tmp/testing/setup.py
  /private/tmp/testing$ /usr/bin/python /private/tmp/testing/setup.py sdist --formats=zip --dist-dir /private/tmp/testing/.tox/dist >/private/tmp/testing/.tox/log/tox-0.log
py35 reusing: /private/tmp/testing/.tox/py35
py35 inst-nodeps: /private/tmp/testing/.tox/dist/Tox Testing-0.0.0.zip
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/pip install -U --no-deps /private/tmp/testing/.tox/dist/Tox Testing-0.0.0.zip >/private/tmp/testing/.tox/py35/log/py35-16.log
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/pip freeze >/private/tmp/testing/.tox/py35/log/py35-17.log
py35 installed: py==1.4.34,pytest==3.2.3,Tox-Testing==0.0.0
py35 runtests: PYTHONHASHSEED='3929104870'
py35 runtests: commands[0] | py.test
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/py.test
================================================================================ test session starts ================================================================================
platform darwin -- Python 3.5.2, pytest-3.2.3, py-1.4.34, pluggy-0.4.0
rootdir: /private/tmp/testing, inifile:
collected 1 item

test_env.py F

===================================================================================== FAILURES ======================================================================================
_____________________________________________________________________________________ test_env ______________________________________________________________________________________

    def test_env():
>       assert os.getenv('XYZ') == 123
E       AssertionError: assert None == 123
E        +  where None = <function getenv at 0x1053daae8>('XYZ')
E        +    where <function getenv at 0x1053daae8> = os.getenv

test_env.py:4: AssertionError
============================================================================= 1 failed in 0.11 seconds ==============================================================================
ERROR: InvocationError: '/private/tmp/testing/.tox/py35/bin/py.test'
______________________________________________________________________________________ summary ______________________________________________________________________________________
ERROR:   py35: commands failed
</code>```

From the doc of setenv it seems it should set the environment variable.

答:
setenv must be in [testenv]:

```<code>[tox]
envlist = py35

[testenv]
deps=pytest
commands=py.test
setenv =
    XYZ = 123
</code>```

Body:
I have tox 2.9.1

$ tox --version
2.9.1 imported from /Library/Python/2.7/site-packages/tox/__init__.pyc
registered plugins:
    tox-pyenv-1.1.0 at /Library/Python/2.7/site-packages/tox_pyenv.pyc

File structure and content are as below

$ tree .
.
├── setup.py
├── test_env.py
└── tox.ini

0 directories, 3 files

setup.py

$ cat setup.py
from setuptools import setup

setup(name="Tox Testing")

tox.ini

$ cat tox.ini
[tox]
envlist = py35
setenv =
    XYZ = 123

[testenv]
deps=pytest
commands=py.test

test_env.py

$ cat test_env.py
import os

def test_env():
    assert os.getenv('XYZ') == 123

When I run the tox command, my test is failing.

$ tox -v
using tox.ini: /private/tmp/testing/tox.ini
using tox-2.9.1 from /Library/Python/2.7/site-packages/tox/__init__.pyc
GLOB sdist-make: /private/tmp/testing/setup.py
  /private/tmp/testing$ /usr/bin/python /private/tmp/testing/setup.py sdist --formats=zip --dist-dir /private/tmp/testing/.tox/dist >/private/tmp/testing/.tox/log/tox-0.log
py35 reusing: /private/tmp/testing/.tox/py35
py35 inst-nodeps: /private/tmp/testing/.tox/dist/Tox Testing-0.0.0.zip
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/pip install -U --no-deps /private/tmp/testing/.tox/dist/Tox Testing-0.0.0.zip >/private/tmp/testing/.tox/py35/log/py35-16.log
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/pip freeze >/private/tmp/testing/.tox/py35/log/py35-17.log
py35 installed: py==1.4.34,pytest==3.2.3,Tox-Testing==0.0.0
py35 runtests: PYTHONHASHSEED='3929104870'
py35 runtests: commands[0] | py.test
  /private/tmp/testing$ /private/tmp/testing/.tox/py35/bin/py.test
================================================================================ test session starts ================================================================================
platform darwin -- Python 3.5.2, pytest-3.2.3, py-1.4.34, pluggy-0.4.0
rootdir: /private/tmp/testing, inifile:
collected 1 item

test_env.py F

===================================================================================== FAILURES ======================================================================================
_____________________________________________________________________________________ test_env ______________________________________________________________________________________

    def test_env():
>       assert os.getenv('XYZ') == 123
E       AssertionError: assert None == 123
E        +  where None = <function getenv at 0x1053daae8>('XYZ')
E        +    where <function getenv at 0x1053daae8> = os.getenv

test_env.py:4: AssertionError
============================================================================= 1 failed in 0.11 seconds ==============================================================================
ERROR: InvocationError: '/private/tmp/testing/.tox/py35/bin/py.test'
______________________________________________________________________________________ summary ______________________________________________________________________________________
ERROR:   py35: commands failed

From the doc of setenv it seems it should set the environment variable.

Body:
setenv must be in [testenv]:

[tox]
envlist = py35

[testenv]
deps=pytest
commands=py.test
setenv =
    XYZ = 123


问:
Xpath in Sql Server
I need some help with some Xpath. I  have an Xml document I need to shred daily (about 500mb) containing transactions on public transport.

An example of the Xml is shown here:

https://docs.google.com/document/d/11Ov6KXo83pu3xEXGovrFK-Hm2cMjZW1gqwTnRTraj58/edit?usp=sharing

The real file contains hundreds of stops and thousands of transactions.

I have a working stored procedure to shred the document here:
https://docs.google.com/document/d/1tegGjaK0x7L3uajeUlD1ex0F-yoeSueujDTarkW_Bak/edit?usp=sharing

The issue is that when I run it on our VM from Sql Server Agent it takes 5 hours.

Ideally this would be done under an hour (which it does run in on my personal machine).

I thought that maybe I could do something like this and do everything in one statement rather than use temp tables and joining:

```<code>SELECT
se.value('@Filename', 'varchar(50)') As [Filename],
se.value('@FareIdentifier', 'varchar(50)') As FareIdentifier,
se.value('@DeviceType', 'varchar(50)') As DeviceType,
ro.value('@ServiceIdentifier', 'varchar(50)') As ServiceIdentifier,
ro.value('@ServiceNumber', 'varchar(50)') As ServiceNumber,
ro.value('@CarrierNumber', 'varchar(50)') AS CarrierNumber,
jo.value('@VehicleRegistration', 'varchar(50)') As VehicleRegistration,
jo.value('@VehicleNumber', 'varchar(50)') As VehicleNumber,
jo.value('@VehicleDepotNumber', 'varchar(50)') As VehicleDepotNumber,
jo.value('@ServiceDepotNumber', 'varchar(50)') As ServiceDepotNumber,
jo.value('@JourneyDirection', 'varchar(50)') As JourneyDirection,
jo.value('@JourneyEnd', 'varchar(50)') As JourneyEnd,
jo.value('@JourneyStart', 'varchar(50)') As JourneyStart,
jo.value('@RunningBoardNumber', 'varchar(50)') As RunningBoardNumber,
jo.value('@RunningBoardDepotNumber', 'varchar(50)') As RunningBoardDepotNumber,
jo.value('@DutyNumber', 'varchar(50)') As DutyNumber,
jo.value('@DutyDepotNumber', 'varchar(50)') As DutyDepotNumber,
jo.value('@DriverName', 'varchar(50)') As DriverName,
jo.value('@DriverNumber', 'varchar(50)') As DriverNumber,
jo.value('@FinalTicketSerial', 'varchar(50)') As FinalTicketSerial,
jo.value('@StartTicketSerial', 'varchar(50)') As StartTicketSerial,
jo.value('@ContractType', 'varchar(50)') As ContractType,
jo.value('@DriverDepotNumber', 'varchar(50)') As DriverDepotNumber,
jo.value('@EtmDepotNumber', 'varchar(50)') As EtmDepotNumber,
jo.value('@Company', 'varchar(50)') As Company,
jo.value('@JourneyType', 'varchar(50)') As JourneyType,
jo.value('@JourneyNumber', 'varchar(50)') As JourneyNumber,
st.value('@TCA', 'varchar(50)') As TCA,
st.value('@Latitude', 'varchar(50)') As Latitude,
st.value('@Longitude', 'varchar(50)') As Longitude,
st.value('@NaptanCode', 'varchar(50)') As NaptanCode,
st.value('@AtcoCode', 'varchar(50)') As ATCOCode,
st.value('@StopName', 'varchar(50)') As StopName,
st.value('@BusStopNumber', 'varchar(50)') As BusStopNumber,
st.value('@StopNumber', 'varchar(50)') As StopNumber,
st.value('@BoardingFareStageName', 'varchar(50)') As BoardingFareStageName,
st.value('@BoardingFareStageNumber', 'varchar(50)') As BoardingFareStageNumber,
st.value('@BoardingFareStageOwner', 'varchar(50)') As BoardingFareStageOwner,
st.value('@RealDeparture', 'varchar(50)') As RealDeparture,
st.value('@RealArrival', 'varchar(50)') As RealArrival,
st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
cs.value('@SoldTicketClassName', 'varchar(50)') As SoldTicketClassName,
cs.value('@SalesPrice', 'varchar(50)') As SalesPrice,
cs.value('@PaymentMethod', 'varchar(50)') As PaymentMethod,
cs.value('@ISOCurrency', 'varchar(50)') As ISOCurrency,
cs.value('@SoldTicketClassNo', 'varchar(50)') As SoldTicketClassNo,
cs.value('@TicketProductName', 'varchar(50)') As TicketProductName,
cs.value('@TicketProductNumber', 'varchar(50)') As TicketProductNumber,
cs.value('@NumTransactions', 'varchar(50)') As NumTransactions,
cs.value('@NumPgersOnTicket', 'varchar(50)') As NumPgersOnTicket,
cs.value('@IssueDateTime', 'varchar(50)') As IssueDateTime,
cs.value('@TicketNumber', 'varchar(50)') As TicketNumber,
cs.value('@TicketGUID', 'varchar(50)') As TicketGUID,
cs.value('@DestinationFareStageName', 'varchar(50)') As DestinationFareStageName,
cs.value('@DestinationFareStageNumber', 'varchar(50)') As DestinationFareStageNumber,
cs.value('@DestinationFareStageOwner', 'varchar(50)') As DestinationFareStageOwner,
cs.value('@EventDateTime', 'varchar(50)') As EventDateTime,
cs.value('@TransactionType', 'varchar(50)') As TransactionType,
cs.value('@CardId', 'varchar(50)') As CardId,
cs.value('@SchemeIdentifier', 'varchar(50)') As SchemeIdentifier

FROM
      XMLUpload CROSS APPLY
      XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
      se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
      ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
      jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
      st.nodes('Events/*')      As [CashSale](cs)
</code>```

This does not work as it creates duplicate lines for the elements CashSale and CardUsage (where on one line CashSale fields are null and on one CardUsage fields are null) when they should be part of the same line.

The ideal result is one record for each CashSale which when they exist have the CardUsage fields on the same line. If there is no CardUsage these fields are null.

Could anyone suggest some optimisation or just point me towards some online resource. I'm struggling to find helpful stuff online.

I have already tried changing the data types to be only the size they need to be which gives me some performance but not as much as I need.

I am open to another solution completely (using powershell or similar maybe) but I couldn't get any others to work with this xml.

答:
Such an XML is hard to query. You have to rely on the implicit order. SQL Server's XQuery is not fully aware of some fancy navigation stuff like siblings and next elements. But you can do a trick: You can shredd this row-wise within a CTE and use a simple NodeIndex=NodeIndex+1 to find the next element. If this is called "CardUsage" you take it, otherwise not.

And please: Keep your examples reduced to the minimum!

```<code>DECLARE @dummyTable TABLE(ID INT IDENTITY, XmlData XML);
INSERT INTO @dummyTable VALUES
 (N'<Header SchemaVersion="2.1" CreationDateTime="20171114044600" ExportCounter="1" DepositDate="20171114" Producer="http://portal.provider.org.uk/v2">
      <Session SessionId="a468dcd3fa963a49b274e64a5c714244" GTCCash="0" GTCRev="0" GTCTrans="0" DepositDateTime="20171112210811" DeviceType="Ticketer ETM" DeviceSerialNumber="0441105106901809" StartDateTime="20171112163000" EndDateTime="20171112210814" FareIdentifier="Ticketer">
        <Routes>
          <Route CarrierNumber="1" ServiceNumber="13" ServiceIdentifier="13">
            <Journey JourneyNumber="639" JourneyType="SRV" Company="1" EtmDepotNumber="188" DriverDepotNumber="188" ContractType="UNK" StartTicketSerial="781" FinalTicketSerial="802" DriverNumber="214373" DutyNumber="214373" RunningBoard="420" JourneyStart="20171112173414" JourneyEnd="20171112190532" JourneyDirection="Outbound" VehicleNumber="67798" VehicleRegistration="67798" DriverName="GARY TAYLOR" VehicleDepotNumber="188" DutyDepotNumber="0" RunningBoardDepotNumber="188" ServiceDepotNumber="0" RunningBoardNumber="3325">
              <DrivenStops>
                <Stop OwnerNumber="1" RealArrival="20171112190057" RealDeparture="20171112190057" BoardingFareStageOwner="0" BoardingFareStageNumber="701" BoardingFareStageName="Town Square (58)" StopNumber="31" BusStopNumber="1" StopName="Town Square" AtcoCode="639001322" NaptanCode="" Latitude="55.165746" Longitude="-2.141343" TCA="">
                  <Events>
                    <CashSale TicketGUID="67177058-d0f8-422e-bb19-9245acea25fa" TicketNumber="802" BoardingFareStageOwner="0" BoardingFareStageNumber="701" BoardingFareStageName="Town Square (58)" DestinationFareStageOwner="0" DestinationFareStageNumber="683" DestinationFareStageName="Golden Lion (60)" IssueDateTime="20171112190057" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="e8d237e2c67aba4bbc922d8daa46ce75" TicketProductName="SC Over 60" SoldTicketClassNo="18285" ISOCurrency="GBP" PaymentMethod="8" SalesPrice="0" ValuePrice="150" SoldTicketClassName="SC Over 60" />
                    <CardUsage SchemeIdentifier="CCS" CardId="63356000903500039" TransactionType="USAGE" EventDateTime="20171112190057" />
                    <CashSale TicketGUID="f7aa4ed4-a385-407d-b116-1c7eb69563be" TicketNumber="811" BoardingFareStageOwner="0" BoardingFareStageNumber="1421" BoardingFareStageName="Town Square (58)" IssueDateTime="20171112193332" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="e49ac3fa349bfc40b2173163538c121e" TicketProductName="QR Accept Ad Wk" SoldTicketClassNo="19137" ISOCurrency="GBP" PaymentMethod="15" SalesPrice="0" SoldTicketClassName="QR Accept Ad Wk" />
                    <CardUsage SchemeIdentifier="Barcode" CardId="d66e1bb355df4668b95be2e4764d30a6" TransactionType="USAGE" EventDateTime="20171112193332" />
                    <CashSale TicketGUID="c6c2a867-676a-4d73-98d6-3a2571892b3d" TicketNumber="812" BoardingFareStageOwner="0" BoardingFareStageNumber="1421" BoardingFareStageName="Town Square (58)" IssueDateTime="20171112193336" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="ed5f87065daf384bab7b0bd9f4b02c80" TicketProductName="Use Wk/M/3M/Yr" SoldTicketClassNo="18724" ISOCurrency="GBP" PaymentMethod="1" SalesPrice="0" SoldTicketClassName="Use Wk/M/3M/Yr" />
                    <CashSale TicketGUID="2c7038ea-0800-4e4e-9d20-35fdb129e317" TicketNumber="828" BoardingFareStageOwner="0" BoardingFareStageNumber="272" BoardingFareStageName="Town Square (58)" DestinationFareStageOwner="0" DestinationFareStageNumber="383" DestinationFareStageName="Long Street (33)" IssueDateTime="20171112204134" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="38e47af2e0a23c4a85eebd96d419feef" TicketProductName="Adult Single" SoldTicketClassNo="18286" ISOCurrency="GBP" PaymentMethod="1" SalesPrice="250" SoldTicketClassName="Adult Single" />
                  </Events>
                </Stop>
              </DrivenStops>
            </Journey>
          </Route>
        </Routes>
      </Session>
    </Header>');
</code>```

--This is your reduced query:

```<code>SELECT
st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
cs.value('@SoldTicketClassName', 'varchar(50)') As SoldTicketClassName,
--please try to reduce your examples!
cs.value('@TicketGUID', 'varchar(50)') As TicketGUID,
cs.value('@CardId', 'varchar(50)') As CardId

FROM
      @dummyTable AS XmlUpload CROSS APPLY
      XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
      se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
      ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
      jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
      st.nodes('Events/*')      As [CashSale](cs);

/*
+-------------+---------------------+--------------------------------------+----------------------------------+
| OwnerNumber | SoldTicketClassName | TicketGUID                           | CardId                           |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | SC Over 60          | 67177058-d0f8-422e-bb19-9245acea25fa | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | NULL                | NULL                                 | 63356000903500039                |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | QR Accept Ad Wk     | f7aa4ed4-a385-407d-b116-1c7eb69563be | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | NULL                | NULL                                 | d66e1bb355df4668b95be2e4764d30a6 |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | Use Wk/M/3M/Yr      | c6c2a867-676a-4d73-98d6-3a2571892b3d | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | Adult Single        | 2c7038ea-0800-4e4e-9d20-35fdb129e317 | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
*/
</code>```

--I assume, that you want the @CardId besides the @TicketGUID - if there is one. Try this:

```<code>WITH InnerNodesCTE AS
(
    SELECT
    st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
    --a running counter
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NodeIndex,
    --the node's name
    cs.value('local-name(.)','nvarchar(100)') AS CsNodeName,
    --the node in one piece
    cs.query('.') AS InnerNode

    FROM
          @dummyTable AS XmlUpload CROSS APPLY
          XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
          se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
          ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
          jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
          st.nodes('Events/*')      As [CashSale](cs)
)
SELECT OwnerNumber,
       InnerNode.value('CashSale[1]/@TicketGUID', 'varchar(50)') As TicketGUID,
       --here you can list all your CashSale attributes
       CardUsageNode.value('CardUsage[1]/@CardId', 'varchar(50)') As CardId
       --here you can list all your CardUsage attributes
FROM InnerNodesCTE AS cs
OUTER APPLY(SELECT cu.InnerNode AS CardUsageNode FROM InnerNodesCTE AS cu WHERE cu.NodeIndex = cs.NodeIndex+1 AND cu.CsNodeName='CardUsage') AS A
WHERE CsNodeName='CashSale'
</code>```

The result

```<code>/*
+---+--------------------------------------+----------------------------------+
| 1 | 67177058-d0f8-422e-bb19-9245acea25fa | 63356000903500039                |
+---+--------------------------------------+----------------------------------+
| 1 | f7aa4ed4-a385-407d-b116-1c7eb69563be | d66e1bb355df4668b95be2e4764d30a6 |
+---+--------------------------------------+----------------------------------+
| 1 | c6c2a867-676a-4d73-98d6-3a2571892b3d | NULL                             |
+---+--------------------------------------+----------------------------------+
| 1 | 2c7038ea-0800-4e4e-9d20-35fdb129e317 | NULL                             |
+---+--------------------------------------+----------------------------------+
*/
</code>```

Title: Xpath in Sql Server

Body:
I need some help with some Xpath. I  have an Xml document I need to shred daily (about 500mb) containing transactions on public transport.

An example of the Xml is shown here:

https://docs.google.com/document/d/11Ov6KXo83pu3xEXGovrFK-Hm2cMjZW1gqwTnRTraj58/edit?usp=sharing

The real file contains hundreds of stops and thousands of transactions.

I have a working stored procedure to shred the document here:
https://docs.google.com/document/d/1tegGjaK0x7L3uajeUlD1ex0F-yoeSueujDTarkW_Bak/edit?usp=sharing

The issue is that when I run it on our VM from Sql Server Agent it takes 5 hours.

Ideally this would be done under an hour (which it does run in on my personal machine).

I thought that maybe I could do something like this and do everything in one statement rather than use temp tables and joining:

SELECT
se.value('@Filename', 'varchar(50)') As [Filename],
se.value('@FareIdentifier', 'varchar(50)') As FareIdentifier,
se.value('@DeviceType', 'varchar(50)') As DeviceType,
ro.value('@ServiceIdentifier', 'varchar(50)') As ServiceIdentifier,
ro.value('@ServiceNumber', 'varchar(50)') As ServiceNumber,
ro.value('@CarrierNumber', 'varchar(50)') AS CarrierNumber,
jo.value('@VehicleRegistration', 'varchar(50)') As VehicleRegistration,
jo.value('@VehicleNumber', 'varchar(50)') As VehicleNumber,
jo.value('@VehicleDepotNumber', 'varchar(50)') As VehicleDepotNumber,
jo.value('@ServiceDepotNumber', 'varchar(50)') As ServiceDepotNumber,
jo.value('@JourneyDirection', 'varchar(50)') As JourneyDirection,
jo.value('@JourneyEnd', 'varchar(50)') As JourneyEnd,
jo.value('@JourneyStart', 'varchar(50)') As JourneyStart,
jo.value('@RunningBoardNumber', 'varchar(50)') As RunningBoardNumber,
jo.value('@RunningBoardDepotNumber', 'varchar(50)') As RunningBoardDepotNumber,
jo.value('@DutyNumber', 'varchar(50)') As DutyNumber,
jo.value('@DutyDepotNumber', 'varchar(50)') As DutyDepotNumber,
jo.value('@DriverName', 'varchar(50)') As DriverName,
jo.value('@DriverNumber', 'varchar(50)') As DriverNumber,
jo.value('@FinalTicketSerial', 'varchar(50)') As FinalTicketSerial,
jo.value('@StartTicketSerial', 'varchar(50)') As StartTicketSerial,
jo.value('@ContractType', 'varchar(50)') As ContractType,
jo.value('@DriverDepotNumber', 'varchar(50)') As DriverDepotNumber,
jo.value('@EtmDepotNumber', 'varchar(50)') As EtmDepotNumber,
jo.value('@Company', 'varchar(50)') As Company,
jo.value('@JourneyType', 'varchar(50)') As JourneyType,
jo.value('@JourneyNumber', 'varchar(50)') As JourneyNumber,
st.value('@TCA', 'varchar(50)') As TCA,
st.value('@Latitude', 'varchar(50)') As Latitude,
st.value('@Longitude', 'varchar(50)') As Longitude,
st.value('@NaptanCode', 'varchar(50)') As NaptanCode,
st.value('@AtcoCode', 'varchar(50)') As ATCOCode,
st.value('@StopName', 'varchar(50)') As StopName,
st.value('@BusStopNumber', 'varchar(50)') As BusStopNumber,
st.value('@StopNumber', 'varchar(50)') As StopNumber,
st.value('@BoardingFareStageName', 'varchar(50)') As BoardingFareStageName,
st.value('@BoardingFareStageNumber', 'varchar(50)') As BoardingFareStageNumber,
st.value('@BoardingFareStageOwner', 'varchar(50)') As BoardingFareStageOwner,
st.value('@RealDeparture', 'varchar(50)') As RealDeparture,
st.value('@RealArrival', 'varchar(50)') As RealArrival,
st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
cs.value('@SoldTicketClassName', 'varchar(50)') As SoldTicketClassName,
cs.value('@SalesPrice', 'varchar(50)') As SalesPrice,
cs.value('@PaymentMethod', 'varchar(50)') As PaymentMethod,
cs.value('@ISOCurrency', 'varchar(50)') As ISOCurrency,
cs.value('@SoldTicketClassNo', 'varchar(50)') As SoldTicketClassNo,
cs.value('@TicketProductName', 'varchar(50)') As TicketProductName,
cs.value('@TicketProductNumber', 'varchar(50)') As TicketProductNumber,
cs.value('@NumTransactions', 'varchar(50)') As NumTransactions,
cs.value('@NumPgersOnTicket', 'varchar(50)') As NumPgersOnTicket,
cs.value('@IssueDateTime', 'varchar(50)') As IssueDateTime,
cs.value('@TicketNumber', 'varchar(50)') As TicketNumber,
cs.value('@TicketGUID', 'varchar(50)') As TicketGUID,
cs.value('@DestinationFareStageName', 'varchar(50)') As DestinationFareStageName,
cs.value('@DestinationFareStageNumber', 'varchar(50)') As DestinationFareStageNumber,
cs.value('@DestinationFareStageOwner', 'varchar(50)') As DestinationFareStageOwner,
cs.value('@EventDateTime', 'varchar(50)') As EventDateTime,
cs.value('@TransactionType', 'varchar(50)') As TransactionType,
cs.value('@CardId', 'varchar(50)') As CardId,
cs.value('@SchemeIdentifier', 'varchar(50)') As SchemeIdentifier

FROM
      XMLUpload CROSS APPLY
      XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
      se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
      ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
      jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
      st.nodes('Events/*')      As [CashSale](cs)

This does not work as it creates duplicate lines for the elements CashSale and CardUsage (where on one line CashSale fields are null and on one CardUsage fields are null) when they should be part of the same line.

The ideal result is one record for each CashSale which when they exist have the CardUsage fields on the same line. If there is no CardUsage these fields are null.

Could anyone suggest some optimisation or just point me towards some online resource. I'm struggling to find helpful stuff online.

I have already tried changing the data types to be only the size they need to be which gives me some performance but not as much as I need.

I am open to another solution completely (using powershell or similar maybe) but I couldn't get any others to work with this xml.

Body:
Such an XML is hard to query. You have to rely on the implicit order. SQL Server's XQuery is not fully aware of some fancy navigation stuff like siblings and next elements. But you can do a trick: You can shredd this row-wise within a CTE and use a simple NodeIndex=NodeIndex+1 to find the next element. If this is called "CardUsage" you take it, otherwise not.

And please: Keep your examples reduced to the minimum!

DECLARE @dummyTable TABLE(ID INT IDENTITY, XmlData XML);
INSERT INTO @dummyTable VALUES
 (N'<Header SchemaVersion="2.1" CreationDateTime="20171114044600" ExportCounter="1" DepositDate="20171114" Producer="http://portal.provider.org.uk/v2">
      <Session SessionId="a468dcd3fa963a49b274e64a5c714244" GTCCash="0" GTCRev="0" GTCTrans="0" DepositDateTime="20171112210811" DeviceType="Ticketer ETM" DeviceSerialNumber="0441105106901809" StartDateTime="20171112163000" EndDateTime="20171112210814" FareIdentifier="Ticketer">
        <Routes>
          <Route CarrierNumber="1" ServiceNumber="13" ServiceIdentifier="13">
            <Journey JourneyNumber="639" JourneyType="SRV" Company="1" EtmDepotNumber="188" DriverDepotNumber="188" ContractType="UNK" StartTicketSerial="781" FinalTicketSerial="802" DriverNumber="214373" DutyNumber="214373" RunningBoard="420" JourneyStart="20171112173414" JourneyEnd="20171112190532" JourneyDirection="Outbound" VehicleNumber="67798" VehicleRegistration="67798" DriverName="GARY TAYLOR" VehicleDepotNumber="188" DutyDepotNumber="0" RunningBoardDepotNumber="188" ServiceDepotNumber="0" RunningBoardNumber="3325">
              <DrivenStops>
                <Stop OwnerNumber="1" RealArrival="20171112190057" RealDeparture="20171112190057" BoardingFareStageOwner="0" BoardingFareStageNumber="701" BoardingFareStageName="Town Square (58)" StopNumber="31" BusStopNumber="1" StopName="Town Square" AtcoCode="639001322" NaptanCode="" Latitude="55.165746" Longitude="-2.141343" TCA="">
                  <Events>
                    <CashSale TicketGUID="67177058-d0f8-422e-bb19-9245acea25fa" TicketNumber="802" BoardingFareStageOwner="0" BoardingFareStageNumber="701" BoardingFareStageName="Town Square (58)" DestinationFareStageOwner="0" DestinationFareStageNumber="683" DestinationFareStageName="Golden Lion (60)" IssueDateTime="20171112190057" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="e8d237e2c67aba4bbc922d8daa46ce75" TicketProductName="SC Over 60" SoldTicketClassNo="18285" ISOCurrency="GBP" PaymentMethod="8" SalesPrice="0" ValuePrice="150" SoldTicketClassName="SC Over 60" />
                    <CardUsage SchemeIdentifier="CCS" CardId="63356000903500039" TransactionType="USAGE" EventDateTime="20171112190057" />
                    <CashSale TicketGUID="f7aa4ed4-a385-407d-b116-1c7eb69563be" TicketNumber="811" BoardingFareStageOwner="0" BoardingFareStageNumber="1421" BoardingFareStageName="Town Square (58)" IssueDateTime="20171112193332" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="e49ac3fa349bfc40b2173163538c121e" TicketProductName="QR Accept Ad Wk" SoldTicketClassNo="19137" ISOCurrency="GBP" PaymentMethod="15" SalesPrice="0" SoldTicketClassName="QR Accept Ad Wk" />
                    <CardUsage SchemeIdentifier="Barcode" CardId="d66e1bb355df4668b95be2e4764d30a6" TransactionType="USAGE" EventDateTime="20171112193332" />
                    <CashSale TicketGUID="c6c2a867-676a-4d73-98d6-3a2571892b3d" TicketNumber="812" BoardingFareStageOwner="0" BoardingFareStageNumber="1421" BoardingFareStageName="Town Square (58)" IssueDateTime="20171112193336" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="ed5f87065daf384bab7b0bd9f4b02c80" TicketProductName="Use Wk/M/3M/Yr" SoldTicketClassNo="18724" ISOCurrency="GBP" PaymentMethod="1" SalesPrice="0" SoldTicketClassName="Use Wk/M/3M/Yr" />
                    <CashSale TicketGUID="2c7038ea-0800-4e4e-9d20-35fdb129e317" TicketNumber="828" BoardingFareStageOwner="0" BoardingFareStageNumber="272" BoardingFareStageName="Town Square (58)" DestinationFareStageOwner="0" DestinationFareStageNumber="383" DestinationFareStageName="Long Street (33)" IssueDateTime="20171112204134" NumPgersOnTicket="1.00" NumTransactions="1" TicketProductNumber="38e47af2e0a23c4a85eebd96d419feef" TicketProductName="Adult Single" SoldTicketClassNo="18286" ISOCurrency="GBP" PaymentMethod="1" SalesPrice="250" SoldTicketClassName="Adult Single" />
                  </Events>
                </Stop>
              </DrivenStops>
            </Journey>
          </Route>
        </Routes>
      </Session>
    </Header>');

--This is your reduced query:

SELECT
st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
cs.value('@SoldTicketClassName', 'varchar(50)') As SoldTicketClassName,
--please try to reduce your examples!
cs.value('@TicketGUID', 'varchar(50)') As TicketGUID,
cs.value('@CardId', 'varchar(50)') As CardId

FROM
      @dummyTable AS XmlUpload CROSS APPLY
      XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
      se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
      ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
      jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
      st.nodes('Events/*')      As [CashSale](cs);

/*
+-------------+---------------------+--------------------------------------+----------------------------------+
| OwnerNumber | SoldTicketClassName | TicketGUID                           | CardId                           |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | SC Over 60          | 67177058-d0f8-422e-bb19-9245acea25fa | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | NULL                | NULL                                 | 63356000903500039                |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | QR Accept Ad Wk     | f7aa4ed4-a385-407d-b116-1c7eb69563be | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | NULL                | NULL                                 | d66e1bb355df4668b95be2e4764d30a6 |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | Use Wk/M/3M/Yr      | c6c2a867-676a-4d73-98d6-3a2571892b3d | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
| 1           | Adult Single        | 2c7038ea-0800-4e4e-9d20-35fdb129e317 | NULL                             |
+-------------+---------------------+--------------------------------------+----------------------------------+
*/

--I assume, that you want the @CardId besides the @TicketGUID - if there is one. Try this:

WITH InnerNodesCTE AS
(
    SELECT
    st.value('@OwnerNumber', 'varchar(50)') As OwnerNumber,
    --a running counter
    ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS NodeIndex,
    --the node's name
    cs.value('local-name(.)','nvarchar(100)') AS CsNodeName,
    --the node in one piece
    cs.query('.') AS InnerNode

    FROM
          @dummyTable AS XmlUpload CROSS APPLY
          XmlData.nodes('Header/Session')   AS [Session](se)        CROSS APPLY
          se.nodes('Routes/Route')          AS [Route](ro)          CROSS APPLY
          ro.nodes('Journey')               As [Journey](jo)        CROSS APPLY
          jo.nodes('DrivenStops/Stop')      As [Stop](st)           CROSS APPLY
          st.nodes('Events/*')      As [CashSale](cs)
)
SELECT OwnerNumber,
       InnerNode.value('CashSale[1]/@TicketGUID', 'varchar(50)') As TicketGUID,
       --here you can list all your CashSale attributes
       CardUsageNode.value('CardUsage[1]/@CardId', 'varchar(50)') As CardId
       --here you can list all your CardUsage attributes
FROM InnerNodesCTE AS cs
OUTER APPLY(SELECT cu.InnerNode AS CardUsageNode FROM InnerNodesCTE AS cu WHERE cu.NodeIndex = cs.NodeIndex+1 AND cu.CsNodeName='CardUsage') AS A
WHERE CsNodeName='CashSale'

The result

/*
+---+--------------------------------------+----------------------------------+
| 1 | 67177058-d0f8-422e-bb19-9245acea25fa | 63356000903500039                |
+---+--------------------------------------+----------------------------------+
| 1 | f7aa4ed4-a385-407d-b116-1c7eb69563be | d66e1bb355df4668b95be2e4764d30a6 |
+---+--------------------------------------+----------------------------------+
| 1 | c6c2a867-676a-4d73-98d6-3a2571892b3d | NULL                             |
+---+--------------------------------------+----------------------------------+
| 1 | 2c7038ea-0800-4e4e-9d20-35fdb129e317 | NULL                             |
+---+--------------------------------------+----------------------------------+
*/


问:
How to download sbt plugin source jars in a common sbt project?
It's nature to download the dependencies source jars in a sbt project, using sbt gen-idea or idea's autoimport feature

but how can I download the sbt plugin source jars which I declared in project/plugins.sbt

答:
IDEA

IDEA should already do this*.

When you import a project and have Download sbt sources checked in Preferences > Build, Execution, Deployment > Build Tools > sbt then it will run the updateSbtClassifiers sbt task.

* Read the Troubleshooting section as there are several known issues with this.

sbt

As mentioned above, you can run the following sbt task:

```<code>sbt> updateSbtClassifiers
</code>```

This command is somewhat special in that it knows to resolve the classifiers for the plugins. It also uses settings that are scoped to that task. See the caveat about sbt/sbt#3432.

Longer way

It is important to understand that sbt is recursive.

Dependencies declared in build.sbt will be for the proper build.

Dependencies declared in project/plugins.sbt will be for the meta-build.

When you just run updateSbtClassifiers it is running this on the proper build, however the dependencies are actually for the meta-build. That is why I said that this task is a little special.

Another way (which can achieve different results) is to run the updateClassifiers task directly on the meta-build.

First switch over to the meta-build:

```<code>sbt> reload plugins
</code>```

Now that you are in the meta-build run:

```<code>sbt:project> updateClassifiers
</code>```

This will retrieve the src and doc for your dependencies (it may not actually do this for some plugins). Run libraryDependencies to see the dependencies of the meta-build.

To get back to the proper

```<code>sbt:project> reload return
</code>```

For a handy all in one command run:

```<code>sbt> updateClassifiers;updateSbtClassifiers;reload plugins;updateClassifiers;updateSbtClassifiers;reload return
</code>```

Troubleshooting

Download failed

You may see in the sbt logs that it failed to download either the src or doc. For example:

```<code>[warn]  ::::::::::::::::::::::::::::::::::::::::::::::
[warn]  ::              FAILED DOWNLOADS            ::
[warn]  :: ^ see resolution messages for details  ^ ::
[warn]  ::::::::::::::::::::::::::::::::::::::::::::::
[warn]  :: rocks.muki#sbt-graphql;0.5.0!sbt-graphql.jar(doc)
[warn]  :: rocks.muki#sbt-graphql;0.5.0!sbt-graphql.jar(src)
[warn]  ::::::::::::::::::::::::::::::::::::::::::::::
</code>```

If you look just above you will see all the resolvers that it tried. You should see one for sbt-plugin-releases which is where most sbt plugins are published to. See predefined resolvers for more details.

If you do not see that it tried sbt-plugin-releases then chances are you have encountered sbt/sbt#3432.

updateSbtClassifiers does not use the correct resolvers. Add the following to your build.sbt file:

```<code>updateSbtClassifiers / dependencyResolution := IvyDependencyResolution((updateSbtClassifiers / ivyConfiguration).value)
</code>```

Sources not attaching

Even if the updateSbtClassifiers successfully downloads and resolves the src and doc IDEA may not attach them.

This seems to be a bug in the sbt-structure plugin. See SCL-13619 for details.

No attempt at downloading

If for some reason this doesn't download the sources (there is no failed download message) then have a look in your .ivy2/exclude_classifiers file. I'm not entirely sure what this file is for but I do know that sbt will exclude anything that is in here.

In my case it had a whole lot of things that I didn't want excluded so I deleted it and then it worked. Delete at your own risk.

Body:
IDEA

IDEA should already do this*.

When you import a project and have Download sbt sources checked in Preferences > Build, Execution, Deployment > Build Tools > sbt then it will run the updateSbtClassifiers sbt task.

* Read the Troubleshooting section as there are several known issues with this.

sbt

As mentioned above, you can run the following sbt task:

sbt> updateSbtClassifiers

This command is somewhat special in that it knows to resolve the classifiers for the plugins. It also uses settings that are scoped to that task. See the caveat about sbt/sbt#3432.

Longer way

It is important to understand that sbt is recursive.

Dependencies declared in build.sbt will be for the proper build.

Dependencies declared in project/plugins.sbt will be for the meta-build.

When you just run updateSbtClassifiers it is running this on the proper build, however the dependencies are actually for the meta-build. That is why I said that this task is a little special.

Another way (which can achieve different results) is to run the updateClassifiers task directly on the meta-build.

First switch over to the meta-build:

sbt> reload plugins

Now that you are in the meta-build run:

sbt:project> updateClassifiers

This will retrieve the src and doc for your dependencies (it may not actually do this for some plugins). Run libraryDependencies to see the dependencies of the meta-build.

To get back to the proper

sbt:project> reload return

For a handy all in one command run:

sbt> updateClassifiers;updateSbtClassifiers;reload plugins;updateClassifiers;updateSbtClassifiers;reload return

Troubleshooting

Download failed

You may see in the sbt logs that it failed to download either the src or doc. For example:

[warn]  ::::::::::::::::::::::::::::::::::::::::::::::
[warn]  ::              FAILED DOWNLOADS            ::
[warn]  :: ^ see resolution messages for details  ^ ::
[warn]  ::::::::::::::::::::::::::::::::::::::::::::::
[warn]  :: rocks.muki#sbt-graphql;0.5.0!sbt-graphql.jar(doc)
[warn]  :: rocks.muki#sbt-graphql;0.5.0!sbt-graphql.jar(src)
[warn]  ::::::::::::::::::::::::::::::::::::::::::::::

If you look just above you will see all the resolvers that it tried. You should see one for sbt-plugin-releases which is where most sbt plugins are published to. See predefined resolvers for more details.

If you do not see that it tried sbt-plugin-releases then chances are you have encountered sbt/sbt#3432.

updateSbtClassifiers does not use the correct resolvers. Add the following to your build.sbt file:

updateSbtClassifiers / dependencyResolution := IvyDependencyResolution((updateSbtClassifiers / ivyConfiguration).value)

Sources not attaching

Even if the updateSbtClassifiers successfully downloads and resolves the src and doc IDEA may not attach them.

This seems to be a bug in the sbt-structure plugin. See SCL-13619 for details.

No attempt at downloading

If for some reason this doesn't download the sources (there is no failed download message) then have a look in your .ivy2/exclude_classifiers file. I'm not entirely sure what this file is for but I do know that sbt will exclude anything that is in here.

In my case it had a whole lot of things that I didn't want excluded so I deleted it and then it worked. Delete at your own risk.