问:
Python Web Scraping using BS
I have a web scraping program that gets multiple pages, but I have to set the while loop to a number. I want to make a condition that stops the loop once it reaches the last page or recognizes there are no more items to scrape. Assume I don't know how many pages exist. How do I change the while loop condition to make it stop without putting a random number?</p>

```<code>import requests
from bs4 import BeautifulSoup
import csv

filename="output.csv"
f=open(filename, 'w', newline="",encoding='utf-8')
headers="Date, Location, Title, Price\n"
f.write(headers)

i=0
while i<5000:
    if i==0:
        page_link="https://portland.craigslist.org/search/sss?query=xbox&sort=date"
    else:
        page_link="https://portland.craigslist.org/search/sss?s={}&query=xbox&sort=date".format(i)
    res=requests.get(page_link)
    soup=BeautifulSoup(res.text,'html.parser')
    for container in soup.select('.result-info'):
        date=container.select('.result-date')[0].text
        try:
            location=container.select('.result-hood')[0].text
        except:
            try:
                location=container.select('.nearby')[0].text
            except:
                location='NULL'
        title=container.select('.result-title')[0].text
        try:
            price=container.select('.result-price')[0].text
        except:
            price="NULL"
        print(date,location,title,price)
        f.write(date+','+location.replace(","," ")+','+title.replace(","," ")+','+price+'\n')
    i+=120
f.close()
</code>```

答:
I use while True to run endless loop and break to exit when there is no data

```<code>    data = soup.select('.result-info')
    if not data:
        print('END: no data:')
        break
</code>```

I use module csv to save data so I don't have to use replace(","," ").

It will put text in " " if there is , in text.

s={} can be in any place after ? so I put it at the end to make code more readable.

Portal gives first page even if you use s=0 so I don't have to check i == 0

(BTW: in my code it has more readable name offset)

Full code.

```<code>import requests
from bs4 import BeautifulSoup
import csv

filename = "output.csv"

f = open(filename, 'w', newline="", encoding='utf-8')

csvwriter = csv.writer(f)

csvwriter.writerow( ["Date", "Location", "Title", "Price"] )

offset = 0

while True:
    print('offset:', offset)

    url = "https://portland.craigslist.org/search/sss?query=xbox&sort=date&s={}".format(offset)

    response = requests.get(url)
    if response.status_code != 200:
        print('END: request status:', response.status)
        break

    soup = BeautifulSoup(response.text, 'html.parser')

    data = soup.select('.result-info')
    if not data:
        print('END: no data:')
        break

    for container in data:
        date = container.select('.result-date')[0].text

        try:
            location = container.select('.result-hood')[0].text
        except:
            try:
                location = container.select('.nearby')[0].text
            except:
                location = 'NULL'
        #location = location.replace(","," ") # don't need it with `csvwriter`

        title = container.select('.result-title')[0].text

        try:
            price = container.select('.result-price')[0].text
        except:
            price = "NULL"
        #title.replace(",", " ") # don't need it with `csvwriter`

        print(date, location, title, price)

        csvwriter.writerow( [date, location, title, price] )

    offset += 120

f.close()
</code>```

Title: Python Web Scraping using BS

Body:
I have a web scraping program that gets multiple pages, but I have to set the while loop to a number. I want to make a condition that stops the loop once it reaches the last page or recognizes there are no more items to scrape. Assume I don't know how many pages exist. How do I change the while loop condition to make it stop without putting a random number?

import requests
from bs4 import BeautifulSoup
import csv

filename="output.csv"
f=open(filename, 'w', newline="",encoding='utf-8')
headers="Date, Location, Title, Price\n"
f.write(headers)

i=0
while i<5000:
    if i==0:
        page_link="https://portland.craigslist.org/search/sss?query=xbox&sort=date"
    else:
        page_link="https://portland.craigslist.org/search/sss?s={}&query=xbox&sort=date".format(i)
    res=requests.get(page_link)
    soup=BeautifulSoup(res.text,'html.parser')
    for container in soup.select('.result-info'):
        date=container.select('.result-date')[0].text
        try:
            location=container.select('.result-hood')[0].text
        except:
            try:
                location=container.select('.nearby')[0].text
            except:
                location='NULL'
        title=container.select('.result-title')[0].text
        try:
            price=container.select('.result-price')[0].text
        except:
            price="NULL"
        print(date,location,title,price)
        f.write(date+','+location.replace(","," ")+','+title.replace(","," ")+','+price+'\n')
    i+=120
f.close()

Body:
I use while True to run endless loop and break to exit when there is no data

    data = soup.select('.result-info')
    if not data:
        print('END: no data:')
        break

I use module csv to save data so I don't have to use replace(","," ").

It will put text in " " if there is , in text.

s={} can be in any place after ? so I put it at the end to make code more readable.

Portal gives first page even if you use s=0 so I don't have to check i == 0

(BTW: in my code it has more readable name offset)

Full code.

import requests
from bs4 import BeautifulSoup
import csv

filename = "output.csv"

f = open(filename, 'w', newline="", encoding='utf-8')

csvwriter = csv.writer(f)

csvwriter.writerow( ["Date", "Location", "Title", "Price"] )

offset = 0

while True:
    print('offset:', offset)

    url = "https://portland.craigslist.org/search/sss?query=xbox&sort=date&s={}".format(offset)

    response = requests.get(url)
    if response.status_code != 200:
        print('END: request status:', response.status)
        break

    soup = BeautifulSoup(response.text, 'html.parser')

    data = soup.select('.result-info')
    if not data:
        print('END: no data:')
        break

    for container in data:
        date = container.select('.result-date')[0].text

        try:
            location = container.select('.result-hood')[0].text
        except:
            try:
                location = container.select('.nearby')[0].text
            except:
                location = 'NULL'
        #location = location.replace(","," ") # don't need it with `csvwriter`

        title = container.select('.result-title')[0].text

        try:
            price = container.select('.result-price')[0].text
        except:
            price = "NULL"
        #title.replace(",", " ") # don't need it with `csvwriter`

        print(date, location, title, price)

        csvwriter.writerow( [date, location, title, price] )

    offset += 120

f.close()


来源: stackexchange

问:
Numpy array creation using a sequence
I have seen this, but it doesn't quite answer my question.

I have an array:

```<code>x = np.array([0, 1, 2])
</code>```

I want this:

```<code>y = np.array([[0,1], [0,2], [1,0], [1,2], [2,0], [2,1]])
</code>```

That is, I want to take each value (let's call it i) of the array x and create x.shape[0]-1 new arrays with all of the other values of x, excluding i.

Essentially y contains the indices of a 3x3 matrix without any diagonal elements.

I have a feeling there's an easy, pythonic way of doing this that's just not coming to me.

答:
Approach #1 : One approach would be -

```<code>x[np.argwhere(~np.eye(len(x),dtype=bool))]
</code>```

Approach #2 : In two steps -

```<code>r = np.arange(len(x))
out = x[np.argwhere(r[:,None]!=r)]
</code>```

Approach #3 : For performance, it might be better to create those pairwise coordinates and then mask. To get the paiwise coordinates, let's use cartesian_product_transpose, like so -

```<code>r = np.arange(len(x))
mask = r[:,None]!=r
out = cartesian_product_transpose(x,x)[mask.ravel()]
</code>```

Approach #4 : Another with np.broadcast_to that avoids making copies until masking, again meant as a performance measure -

```<code>n = len(x)
r = np.arange(n)
mask = r[:,None]!=r
c0 = np.broadcast_to(x[:,None], (n, n))[mask]
c1 = np.broadcast_to(x, (n,n))[mask]
out = np.column_stack((c0,c1))
</code>```

Runtime test -

```<code>In [382]: x = np.random.randint(0,9,(1000))

# @tom10's soln
In [392]: %timeit list(itertools.permutations(x, 2))
10 loops, best of 3: 62 ms per loop

In [383]: %%timeit
     ...: x[np.argwhere(~np.eye(len(x),dtype=bool))]
100 loops, best of 3: 11.4 ms per loop

In [384]: %%timeit
     ...: r = np.arange(len(x))
     ...: out = x[np.argwhere(r[:,None]!=r)]
100 loops, best of 3: 12.9 ms per loop

In [388]: %%timeit
     ...: r = np.arange(len(x))
     ...: mask = r[:,None]!=r
     ...: out = cartesian_product_transpose(x,x)[mask.ravel()]
100 loops, best of 3: 16.5 ms per loop

In [389]: %%timeit
     ...: n = len(x)
     ...: r = np.arange(n)
     ...: mask = r[:,None]!=r
     ...: c0 = np.broadcast_to(x[:,None], (n, n))[mask]
     ...: c1 = np.broadcast_to(x, (n,n))[mask]
     ...: out = np.column_stack((c0,c1))
100 loops, best of 3: 6.72 ms per loop
</code>```

ContentLicense: CC BY-SA 3.0

Title: Numpy array creation using a sequence

Body:
I have seen this, but it doesn't quite answer my question.

I have an array:

x = np.array([0, 1, 2])

I want this:

y = np.array([[0,1], [0,2], [1,0], [1,2], [2,0], [2,1]])

That is, I want to take each value (let's call it i) of the array x and create x.shape[0]-1 new arrays with all of the other values of x, excluding i.

Essentially y contains the indices of a 3x3 matrix without any diagonal elements.

I have a feeling there's an easy, pythonic way of doing this that's just not coming to me.

Body:
Approach #1 : One approach would be -

x[np.argwhere(~np.eye(len(x),dtype=bool))]

Approach #2 : In two steps -

r = np.arange(len(x))
out = x[np.argwhere(r[:,None]!=r)]

Approach #3 : For performance, it might be better to create those pairwise coordinates and then mask. To get the paiwise coordinates, let's use cartesian_product_transpose, like so -

r = np.arange(len(x))
mask = r[:,None]!=r
out = cartesian_product_transpose(x,x)[mask.ravel()]

Approach #4 : Another with np.broadcast_to that avoids making copies until masking, again meant as a performance measure -

n = len(x)
r = np.arange(n)
mask = r[:,None]!=r
c0 = np.broadcast_to(x[:,None], (n, n))[mask]
c1 = np.broadcast_to(x, (n,n))[mask]
out = np.column_stack((c0,c1))

Runtime test -

In [382]: x = np.random.randint(0,9,(1000))

# @tom10's soln
In [392]: %timeit list(itertools.permutations(x, 2))
10 loops, best of 3: 62 ms per loop

In [383]: %%timeit
     ...: x[np.argwhere(~np.eye(len(x),dtype=bool))]
100 loops, best of 3: 11.4 ms per loop

In [384]: %%timeit
     ...: r = np.arange(len(x))
     ...: out = x[np.argwhere(r[:,None]!=r)]
100 loops, best of 3: 12.9 ms per loop

In [388]: %%timeit
     ...: r = np.arange(len(x))
     ...: mask = r[:,None]!=r
     ...: out = cartesian_product_transpose(x,x)[mask.ravel()]
100 loops, best of 3: 16.5 ms per loop

In [389]: %%timeit
     ...: n = len(x)
     ...: r = np.arange(n)
     ...: mask = r[:,None]!=r
     ...: c0 = np.broadcast_to(x[:,None], (n, n))[mask]
     ...: c1 = np.broadcast_to(x, (n,n))[mask]
     ...: out = np.column_stack((c0,c1))
100 loops, best of 3: 6.72 ms per loop


问:
Stripe Custom Checkout With Options
How can you add a size option to Stripe Custom Checkout?
As of right now I have this for using the custom checkout with a script tag.</p>

```<code><?php
require('config.php');

?>
  <form action="charge.php" method="post">
      <script src="https://checkout.stripe.com/checkout.js" class="stripe-button"
        data-key="<?php echo $stripe['publishable_key']?>"
        data-amount="3500"
        data-locale="auto"
        data-company="Company Name"
        data-billingAddress="true"
        data-email="true">
      </script>
  </form>
</code>```

答:
You cannot.

This is because Stripe embeds the checkout inside an iframe element. It does this so that you cannot access the card details from your own js context. This keeps you PCI compliant without having to deal with any of that stuff.

If you want a more customizable experience either tokenize the cards yourself without using their checkout. Or use the new Elements API to create a hybrid that will give you a lot more style control, with less setup and many nice to have features.

Title: Stripe Custom Checkout With Options

Body:
How can you add a size option to Stripe Custom Checkout?
As of right now I have this for using the custom checkout with a script tag.

<?php
require('config.php');

?>
  <form action="charge.php" method="post">

  </form>


问:
Optimal way to filter the left join result in oracle

I'm doing a select using 4 tables and one of them has 6.7M rows. In the table_1 I have some main codes, and in the table_2 (the one with 6.7M rows) I have subcodes of the main codes, like this:

```<code>Table_1      Table_2
| code |      | code |
| A1   |      | A1A  |
| A11  |      | A1B  |
| A2   |      | A2A  |
| A22  |      | A22  |
</code>```

The rule is, the code always has one or two letter and a number (with 1, 2 or 3 digits), and the subcode is the same code, or has the same code + one letter. So, I did the left join like this:

```<code>SELECT t1.code, t2.subcode
FROM table_1 t1
LEFT JOIN table_2 t2
ON t2.subcode LIKE t1.code || '%' AND
substr(replace(t2.subcode, t1.code, ''), 1, 1) not in ('0', '1', '2', '3',
'4', '5', '6', '7', '8', '9')
</code>```

It's works, but, because Table_2 has 6.7M results, even with the 2 columns having indexes, the query (with joins with the other 2 tables) it's taking 6~7 minutes to resolve and I need this in a shorter time.
So, someone knows how to optimize this query?

答:
I don't like the use of like and in in your join conditions.  How about using something like:

```<code>SELECT t1.code, t2.subcode
  FROM table_1 t1
       LEFT JOIN table_2 t2
                 ON t2.subcode = t1.code || 'A' OR
                    t2.subcode = t1.code || 'B' OR ...
</code>```

if you only have a few subcodes, only the first few letters of the alphabet, doing it like this might be a lot faster.  Or, you could try

```<code>SELECT t1.code, t2.subcode
  FROM table_1 t1
       LEFT JOIN table_2 t2
            ON SUBSTR(t2.subcode, 0, LENGTH(t1.code)) == t1.code AND
               SUBSTR(t2.subcode, LENGTH(t1.code) + 1, LENGTH(t2.subcode)-LENGTH(t1.code)) IN ('A', 'B', 'C', 'D'...)
</code>```

Body:
I'm doing a select using 4 tables and one of them has 6.7M rows. In the table_1 I have some main codes, and in the table_2 (the one with 6.7M rows) I have subcodes of the main codes, like this:

Table_1      Table_2
| code |      | code |
| A1   |      | A1A  |
| A11  |      | A1B  |
| A2   |      | A2A  |
| A22  |      | A22  |

The rule is, the code always has one or two letter and a number (with 1, 2 or 3 digits), and the subcode is the same code, or has the same code + one letter. So, I did the left join like this:

SELECT t1.code, t2.subcode
FROM table_1 t1
LEFT JOIN table_2 t2
ON t2.subcode LIKE t1.code || '%' AND
substr(replace(t2.subcode, t1.code, ''), 1, 1) not in ('0', '1', '2', '3',
'4', '5', '6', '7', '8', '9')

It's works, but, because Table_2 has 6.7M results, even with the 2 columns having indexes, the query (with joins with the other 2 tables) it's taking 6~7 minutes to resolve and I need this in a shorter time.
So, someone knows how to optimize this query?

Body:
I don't like the use of like and in in your join conditions.  How about using something like:

SELECT t1.code, t2.subcode
  FROM table_1 t1
       LEFT JOIN table_2 t2
                 ON t2.subcode = t1.code || 'A' OR
                    t2.subcode = t1.code || 'B' OR ...

if you only have a few subcodes, only the first few letters of the alphabet, doing it like this might be a lot faster.  Or, you could try

SELECT t1.code, t2.subcode
  FROM table_1 t1
       LEFT JOIN table_2 t2
            ON SUBSTR(t2.subcode, 0, LENGTH(t1.code)) == t1.code AND
               SUBSTR(t2.subcode, LENGTH(t1.code) + 1, LENGTH(t2.subcode)-LENGTH(t1.code)) IN ('A', 'B', 'C', 'D'...)


答:
Create an index on table2 just on the code:

```<code>create index idx_table2_code
    on table2(case when substr(code, 3, 1) between 'A' and 'Z'
                   then substr(code, 1, 2) else code
              end);
</code>```

Then use this as the expression in the join:

```<code>select t1.code, t2.subcode
from table_1 t1 left join
     table_2 t2
     on t1.code = (case when substr(code, 3, 1) between 'A' and 'Z'
                        then substr(code, 1, 2) else code
                   end);
</code>```

The join should be able to use the index.

Title: Optimal way to filter the left join result in oracle

Body:
Create an index on table2 just on the code:

create index idx_table2_code
    on table2(case when substr(code, 3, 1) between 'A' and 'Z'
                   then substr(code, 1, 2) else code
              end);

Then use this as the expression in the join:

select t1.code, t2.subcode
from table_1 t1 left join
     table_2 t2
     on t1.code = (case when substr(code, 3, 1) between 'A' and 'Z'
                        then substr(code, 1, 2) else code
                   end);

The join should be able to use the index.


问:
Is using regex for a request handler in PHP bad?
I am working on a request handler to route every page call through my index page and have SEO friendly urls.

domain.com/account/settings

this would be easy to map to the correct page but some are more complex when there becomes an ID number or a page number in the uri.

So I see some people will use things like preg_match and cycle through an array of patterns ->  uri to get a match which is nice for when paging and id's come into play but from my experience, it seems like running preg_match on an array of say 20 items on every page load is not good for performace.

Please tell me your thought on this?

答:
So I see some people will use things
  like preg_match and cycle through an
  array of patterns -> uri

I think, such approach is OK. For example, Drupal and Django frameworks do exactly that.

Another alternative is using URL Rewrite Engine (see, for example, this question for details).

ContentLicense: CC BY-SA 2.5


问:
Pointer changes at runtime
I have the following file test.c:

```<code>#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#define LEXER_INC(l) ( (l)->pos++ )

#define isCidstart(c) (isalpha(c) || (c)=='_')
#define isCident(c) (isalnum(c) || (c)=='_')

typedef struct LexerState
{
    const char *fileName;
    const char *sourceText;
    int sourceLength;
    const char *pos;
    const char *end;
    int line;
} LexerState;

typedef enum LexerToken
{
    TokenHalt,
    TokenPush,
    TokenPop,
    TokenEndOfLine,
    TokenEOF,
    TokenNone
} LexerToken;

typedef struct ReservedWord
{
    const char *word;
    LexerToken token;
} ReservedWord;

static ReservedWord reservedWords[] =
{
    {"halt", TokenHalt},
    {"push", TokenPush},
    {"pop", TokenPop}
};

void lexerInit(LexerState *lexer)
{
    lexer->fileName = "test.s";
    lexer->sourceText = "pop\r\npush\r\nhalt\r\n"; // read from file
    lexer->sourceLength = strlen(lexer->sourceText); // 17
    lexer->pos = lexer->sourceText; // pointing to first char of lexer->sourceText
    lexer->end = lexer->sourceText + lexer->sourceLength; // end of lexer->sourceText string, i.e. '\0'
    lexer->line = 1;
}

LexerToken lexerCheckReservedWord(const char *word)
{
    for (int count = 0; count < (sizeof(reservedWords) / sizeof(ReservedWord)); count++)
    {
        if (!strcmp(word, reservedWords[count].word))
            return reservedWords[count].token;
    }

    return TokenNone;
}

LexerToken lexerGetWord(LexerState *lexer)
{
    const char *startPos = lexer->pos;
    LexerToken token = TokenNone;
    char *word;
    int len;

    do
    {
        LEXER_INC(lexer);
    }
    while (lexer->pos != lexer->end && isCident(*lexer->pos));

    len = lexer->pos - startPos;
    word = malloc(len + 1); /* (len + 1) for '\0' ending */
    strncpy(word, startPos, len);
    word[len] = '\0';

    token = lexerCheckReservedWord(word);

    return token;
}

LexerToken lexerGetToken(LexerState *lexer)
{
    char thisChar;

    /* Skip white characters */
    while (lexer->pos != lexer->end && isspace(*lexer->pos))
    {
        if (*lexer->pos == '\n')
        {
            /* New line found */
            lexer->line++;
            LEXER_INC(lexer);
            return TokenEndOfLine;
        }

        LEXER_INC(lexer);
    }

    if (lexer->pos == lexer->end || *lexer->pos == '\0')
    {
        return TokenEOF;
    }

    thisChar = *lexer->pos;

    if (isCidstart(thisChar))
    {
        return lexerGetWord(lexer);
    }

    return TokenEOF;
}

int main(int argc, const char *argv[])
{
    LexerState *lexer;
    LexerToken token;

    lexer = malloc(sizeof(lexer));
    lexerInit(lexer);

    while ((token = lexerGetToken(lexer)) != TokenEOF)
    {
        printf("token %d\n", token);
    }

    return EXIT_SUCCESS;
}
</code>```

When debugging with gdb the function lexerGetWord, I realised that after executing the line word = malloc((len + 1) * sizeof(char));, the lexer->pos pointer address changes to some number (in my case 0x23):

```<code>82              len = lexer->pos - startPos;
(gdb) next
83              word = malloc(len + 1);   // (len + 1) for '\0' ending
(gdb) print len
$1 = 3
(gdb) print lexer->pos
$2 = 0x60003b1b3 "\r\npush\r\nhalt\r\n"
</code>```

Here lexer->pos points to this address after getting word "pop" from the string lexer->sourceText ("pop\r\npush\r\nhalt\r\n")

```<code>(gdb) next
84              strncpy(word, startPos, len);
(gdb) print lexer->pos
$3 = 0x23 <error: Cannot access memory at address 0x23>
</code>```

But after malloc is executed, the pointer changes it address, resulting later in Segmentation fault.

```<code>$ gcc test.c -o test
$ ./test
token 2
Segmentation fault (core dumped)
</code>```

What I'm doing wrong?

EDIT

This only happens with Cygwin gcc. I tried MinGW and all works fine.

```<code>$ uname -a
CYGWIN_NT-6.3 Stepan 2.9.0(0.318/5/3) 2017-09-12 10:18 x86_64 Cygwin
</code>```

答:
The problem is in main:

```<code>lexer = malloc(sizeof(lexer));
</code>```

You're allocating enough space for the pointer, not what it points to.  As a result, you end up writing past the end of allocated memory.  This invokes undefined behavior.

You need space for what lexer points to:

```<code>lexer = malloc(sizeof(*lexer));
</code>```

Title: Pointer changes at runtime

Body:
I have the following file test.c:

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>

#define LEXER_INC(l) ( (l)->pos++ )

#define isCidstart(c) (isalpha(c) || (c)=='_')
#define isCident(c) (isalnum(c) || (c)=='_')

typedef struct LexerState
{
    const char *fileName;
    const char *sourceText;
    int sourceLength;
    const char *pos;
    const char *end;
    int line;
} LexerState;

typedef enum LexerToken
{
    TokenHalt,
    TokenPush,
    TokenPop,
    TokenEndOfLine,
    TokenEOF,
    TokenNone
} LexerToken;

typedef struct ReservedWord
{
    const char *word;
    LexerToken token;
} ReservedWord;

static ReservedWord reservedWords[] =
{
    {"halt", TokenHalt},
    {"push", TokenPush},
    {"pop", TokenPop}
};

void lexerInit(LexerState *lexer)
{
    lexer->fileName = "test.s";
    lexer->sourceText = "pop\r\npush\r\nhalt\r\n"; // read from file
    lexer->sourceLength = strlen(lexer->sourceText); // 17
    lexer->pos = lexer->sourceText; // pointing to first char of lexer->sourceText
    lexer->end = lexer->sourceText + lexer->sourceLength; // end of lexer->sourceText string, i.e. '\0'
    lexer->line = 1;
}

LexerToken lexerCheckReservedWord(const char *word)
{
    for (int count = 0; count < (sizeof(reservedWords) / sizeof(ReservedWord)); count++)
    {
        if (!strcmp(word, reservedWords[count].word))
            return reservedWords[count].token;
    }

    return TokenNone;
}

LexerToken lexerGetWord(LexerState *lexer)
{
    const char *startPos = lexer->pos;
    LexerToken token = TokenNone;
    char *word;
    int len;

    do
    {
        LEXER_INC(lexer);
    }
    while (lexer->pos != lexer->end && isCident(*lexer->pos));

    len = lexer->pos - startPos;
    word = malloc(len + 1); /* (len + 1) for '\0' ending */
    strncpy(word, startPos, len);
    word[len] = '\0';

    token = lexerCheckReservedWord(word);

    return token;
}

LexerToken lexerGetToken(LexerState *lexer)
{
    char thisChar;

    /* Skip white characters */
    while (lexer->pos != lexer->end && isspace(*lexer->pos))
    {
        if (*lexer->pos == '\n')
        {
            /* New line found */
            lexer->line++;
            LEXER_INC(lexer);
            return TokenEndOfLine;
        }

        LEXER_INC(lexer);
    }

    if (lexer->pos == lexer->end || *lexer->pos == '\0')
    {
        return TokenEOF;
    }

    thisChar = *lexer->pos;

    if (isCidstart(thisChar))
    {
        return lexerGetWord(lexer);
    }

    return TokenEOF;
}

int main(int argc, const char *argv[])
{
    LexerState *lexer;
    LexerToken token;

    lexer = malloc(sizeof(lexer));
    lexerInit(lexer);

    while ((token = lexerGetToken(lexer)) != TokenEOF)
    {
        printf("token %d\n", token);
    }

    return EXIT_SUCCESS;
}

When debugging with gdb the function lexerGetWord, I realised that after executing the line word = malloc((len + 1) * sizeof(char));, the lexer->pos pointer address changes to some number (in my case 0x23):

82              len = lexer->pos - startPos;
(gdb) next
83              word = malloc(len + 1);   // (len + 1) for '\0' ending
(gdb) print len
$1 = 3
(gdb) print lexer->pos
$2 = 0x60003b1b3 "\r\npush\r\nhalt\r\n"

Here lexer->pos points to this address after getting word "pop" from the string lexer->sourceText ("pop\r\npush\r\nhalt\r\n")

(gdb) next
84              strncpy(word, startPos, len);
(gdb) print lexer->pos
$3 = 0x23 <error: Cannot access memory at address 0x23>

But after malloc is executed, the pointer changes it address, resulting later in Segmentation fault.

$ gcc test.c -o test
$ ./test
token 2
Segmentation fault (core dumped)

What I'm doing wrong?

EDIT

This only happens with Cygwin gcc. I tried MinGW and all works fine.

$ uname -a
CYGWIN_NT-6.3 Stepan 2.9.0(0.318/5/3) 2017-09-12 10:18 x86_64 Cygwin

Body:
The problem is in main:

lexer = malloc(sizeof(lexer));

You're allocating enough space for the pointer, not what it points to.  As a result, you end up writing past the end of allocated memory.  This invokes undefined behavior.

You need space for what lexer points to:

lexer = malloc(sizeof(*lexer));


问:
Compute lag difference for different days
I need help computing a date difference across different rows with variable lag (specifically, rows that are not on the same day) without subqueries, joins, etc. I think this should be possible with some inline t-SQL aggregates that use OVER(PARTITION BY) clause, such as LAG, DENSE_RANK, etc., but I can't quite put a finger on it. This is for a SQL Server 2017 Developer's edition.

A clarifying example:

Consider a dataset with Job beginning and end dates (across various projects). Some jobs start and end on the same day (such as jobs 2 & 3, 4 & 5). I need to compute the idle time between consequent jobs that started on different days (per project). That is the days between last job's ending time and current job's beginning time. If the previous job started on the same day, then look further back in history of the same project. I.e. the jobs that started on the same day can be considered as parts of the same job.

UPDATE: I simplified the code/output by dropping time values (question's history has original dataset).

```<code>IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(Prj TINYINT, Beg DATE, Eñd DATE);
INSERT INTO #t SELECT 1, '1/1/17', '1/2/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/15/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/18/17';
INSERT INTO #t SELECT 1, '1/20/17', '1/24/17';
INSERT INTO #t SELECT 2, '2/2/17', '2/5/17';
INSERT INTO #t SELECT 2, '2/7/17', '2/9/17';
ALTER TABLE #t ADD Job INT NOT NULL IDENTITY (1,1) PRIMARY KEY;
</code>```

A LAG(.,1) function uses precisely the previous job's ending time, which is not what I want. It yields incorrect idle duration for jobs 2 & 3, 4 & 5. Jobs 2 & 3 should both use the ending time of job 1. Jobs 4 & 5 should both use the ending time of job 3. The joined query computes idle duration correctly, but an inline calculation is desirable here (without joins, subqueries).

```<code>SELECT c.Job, c.Prj, c.Beg, c.Eñd,
-- in-line computation with OVER clause
PrvEñd_lg=LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg),
Idle_lg=DATEDIFF(DAY, LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg), c.Beg),
-- calculation over current and (joined) previous records
PrvEñd_j=MAX(p.Eñd),
IdleDur_j=DATEDIFF(DAY, MAX(p.Eñd), c.Beg)
FROM #t c LEFT JOIN #t p ON c.Prj=p.Prj AND c.Beg > p.Eñd
GROUP BY c.Job, c.Prj, c.Beg, c.Eñd
ORDER BY c.Prj, c.Beg

Job Prj Beg         Eñd         PrvEñd_lg   Idle_lg PrvEñd_j    IdleDur_j
1   1   2017-01-01  2017-01-02  NULL        NULL    NULL        NULL
2   1   2017-01-05  2017-01-07  2017-01-02  3       2017-01-02  3
3   1   2017-01-05  2017-01-07  2017-01-07  -2      2017-01-02  3
4   1   2017-01-15  2017-01-15  2017-01-07  8       2017-01-07  8
5   1   2017-01-15  2017-01-18  2017-01-15  0       2017-01-07  8
6   1   2017-01-20  2017-01-24  2017-01-18  2       2017-01-18  2
7   2   2017-02-02  2017-02-05  NULL        NULL    NULL        NULL
8   2   2017-02-07  2017-02-09  2017-02-05  2       2017-02-05  2
</code>```

Please let me know, if I can further clarify any specific details.

Many thanks!

Body:
I need help computing a date difference across different rows with variable lag (specifically, rows that are not on the same day) without subqueries, joins, etc. I think this should be possible with some inline t-SQL aggregates that use OVER(PARTITION BY) clause, such as LAG, DENSE_RANK, etc., but I can't quite put a finger on it. This is for a SQL Server 2017 Developer's edition.

A clarifying example:

Consider a dataset with Job beginning and end dates (across various projects). Some jobs start and end on the same day (such as jobs 2 & 3, 4 & 5). I need to compute the idle time between consequent jobs that started on different days (per project). That is the days between last job's ending time and current job's beginning time. If the previous job started on the same day, then look further back in history of the same project. I.e. the jobs that started on the same day can be considered as parts of the same job.

UPDATE: I simplified the code/output by dropping time values (question's history has original dataset).

IF OBJECT_ID('tempdb..#t') IS NOT NULL DROP TABLE #t;
CREATE TABLE #t(Prj TINYINT, Beg DATE, Eñd DATE);
INSERT INTO #t SELECT 1, '1/1/17', '1/2/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/5/17', '1/7/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/15/17';
INSERT INTO #t SELECT 1, '1/15/17', '1/18/17';
INSERT INTO #t SELECT 1, '1/20/17', '1/24/17';
INSERT INTO #t SELECT 2, '2/2/17', '2/5/17';
INSERT INTO #t SELECT 2, '2/7/17', '2/9/17';
ALTER TABLE #t ADD Job INT NOT NULL IDENTITY (1,1) PRIMARY KEY;

A LAG(.,1) function uses precisely the previous job's ending time, which is not what I want. It yields incorrect idle duration for jobs 2 & 3, 4 & 5. Jobs 2 & 3 should both use the ending time of job 1. Jobs 4 & 5 should both use the ending time of job 3. The joined query computes idle duration correctly, but an inline calculation is desirable here (without joins, subqueries).

SELECT c.Job, c.Prj, c.Beg, c.Eñd,
-- in-line computation with OVER clause
PrvEñd_lg=LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg),
Idle_lg=DATEDIFF(DAY, LAG(c.Eñd,1) OVER(PARTITION BY c.Prj ORDER BY c.Beg), c.Beg),
-- calculation over current and (joined) previous records
PrvEñd_j=MAX(p.Eñd),
IdleDur_j=DATEDIFF(DAY, MAX(p.Eñd), c.Beg)
FROM #t c LEFT JOIN #t p ON c.Prj=p.Prj AND c.Beg > p.Eñd
GROUP BY c.Job, c.Prj, c.Beg, c.Eñd
ORDER BY c.Prj, c.Beg

Job Prj Beg         Eñd         PrvEñd_lg   Idle_lg PrvEñd_j    IdleDur_j
1   1   2017-01-01  2017-01-02  NULL        NULL    NULL        NULL
2   1   2017-01-05  2017-01-07  2017-01-02  3       2017-01-02  3
3   1   2017-01-05  2017-01-07  2017-01-07  -2      2017-01-02  3
4   1   2017-01-15  2017-01-15  2017-01-07  8       2017-01-07  8
5   1   2017-01-15  2017-01-18  2017-01-15  0       2017-01-07  8
6   1   2017-01-20  2017-01-24  2017-01-18  2       2017-01-18  2
7   2   2017-02-02  2017-02-05  NULL        NULL    NULL        NULL
8   2   2017-02-07  2017-02-09  2017-02-05  2       2017-02-05  2

Please let me know, if I can further clarify any specific details.

Many thanks!

Body:
You can use a self-join.

select a.Job
, a.Prj
, a.Beg
, a.Eñd
, max(b.Eñd) as PrevEñd
, min(datediff(mi, b.Eñd, a.Beg) / (60*24.0)) as IdleDur
from #t as a
left join #t as b on a.Prj = b.Prj
                 and cast(a.Beg as date) > cast(b.Eñd as date)
group by a.Job
, a.Prj
, a.Beg
, a.Eñd

This produces the following output:

+-----+-----+---------------------+---------------------+---------------------+-----------+
| Job | Prj |         Beg         |         Eñd         |       PrevEñd       |  IdleDur  |
+-----+-----+---------------------+---------------------+---------------------+-----------+
|   1 |   1 | 2017-01-01 01:00:00 | 2017-01-02 02:00:00 | NULL                | NULL      |
|   2 |   1 | 2017-01-05 02:00:00 | 2017-01-07 03:00:00 | 2017-01-02 02:00:00 | 3.0000000 |
|   3 |   1 | 2017-01-05 03:00:00 | 2017-01-07 02:00:00 | 2017-01-02 02:00:00 | 3.0416666 |
|   4 |   1 | 2017-01-15 04:00:00 | 2017-01-15 03:00:00 | 2017-01-07 03:00:00 | 8.0416666 |
|   5 |   1 | 2017-01-15 15:00:00 | 2017-01-18 03:00:00 | 2017-01-07 03:00:00 | 8.5000000 |
|   6 |   1 | 2017-01-20 05:00:00 | 2017-01-24 02:00:00 | 2017-01-18 03:00:00 | 2.0833333 |
|   7 |   2 | 2017-02-02 06:00:00 | 2017-02-05 03:00:00 | NULL                | NULL      |
|   8 |   2 | 2017-02-07 07:00:00 | 2017-02-09 02:00:00 | 2017-02-05 03:00:00 | 2.1666666 |
+-----+-----+---------------------+---------------------+---------------------+-----------+


问:
Select all open orders from an order table and its associated state table
Premise:

Orders table - id, rate, unit, amount, timestamp

Order States table - id, order_id, state, timestamp

Both tables are insert only (no updates)

For each order created, the state table can possibly have one or more states of the order, like 0 for open, 1 for completed etc.

Purpose:

Retrieve all open orders

Retrieve all open orders order by rate desc/asc

The same is currently achieved using subquery, group by and having clause. Also tried joins, group by and having clause.

Problem:

Very slow select time - Around 500 to 1000 ms for an order table size of approx a million records.

Required Help:

Indexing suggestions

Query re-write suggestions

Any help greatly appreciated.

答:
Without knowing the tables structure (indexes and constraints).
If your Order States table  contains every state each order went through and considering the queries you need to run, i would suggest to maintain a table with only the last state (which is the current state too i guess)  of each order and keep the previous states in a history table.

without knowing what you already have

Orders table

id: primary key (automatically indexed using b-tree)

rate: indexed (because you need to sort your result by rate)

Order States table

id: primary key (automatically indexed using b-tree)

order_id: foreign key + index

state: index

maybe an index order_id,state could help too (give it a try)


问:
My Java code is not running
If I am not mistaken, this bit of code should print out all even numbers smaller than or equal to 100. When I run this code, nothing happens. No error message or anything. I'm using Eclipse.</p>

```<code>public class Even {

    public static void main(String args[]) {

        int number = 1;
        int remainder = number % 2;

        while(number <= 100) {
            number++;
            if(remainder == 0) {
                System.out.println(number);
            }
        }

    }

}
</code>```

答:
As others stated before, value of remainder is set only once. However, you check it a hundred times expecting it to tell you something else every time. I'd suggest putting it inside a loop, so you get a "fresh" value for every number.

```<code>public class Even {

public static void main(String args[]) {

    int number = 1;
    int remainder;

    while(number <= 100) {
        number++;
        remainder = number % 2;
        if(remainder == 0) {
            System.out.println(number);
        }
    }

}

}
</code>```

Title: My Java code is not running

Body:
If I am not mistaken, this bit of code should print out all even numbers smaller than or equal to 100. When I run this code, nothing happens. No error message or anything. I'm using Eclipse.

public class Even {

    public static void main(String args[]) {

        int number = 1;
        int remainder = number % 2;

        while(number <= 100) {
            number++;
            if(remainder == 0) {
                System.out.println(number);
            }
        }

    }

}

Body:
As others stated before, value of remainder is set only once. However, you check it a hundred times expecting it to tell you something else every time. I'd suggest putting it inside a loop, so you get a "fresh" value for every number.

public class Even {

public static void main(String args[]) {

    int number = 1;
    int remainder;

    while(number <= 100) {
        number++;
        remainder = number % 2;
        if(remainder == 0) {
            System.out.println(number);
        }
    }

}

}


问:
chat web app: flex-boxes inside div position:fixed
I have a chat web app with a bottom bar consisting of: button, text entry, button:

```<code><div id="bottom-bar">
  <div class="button-left">
    <img src="https://placeholdit.co//i/40x40">
  </div>
  <div class="textentry">
    <input type="text" id="chatmsg" name="chatmsg">
  </div>
  <div class="button-right">
    <img src="https://placeholdit.co//i/40x40">
  </div>
</div>

#bottom-bar {
    position: fixed;
    bottom: 0px;
    height: 40px;
    width: 100%;
}
</code>```

What I want to achieve: (1) place the left button to the left of the bottom-bar, (2) place the right button to the right of the bottom-bar and (3) have an input field that stretches in the middle using all the space that is available.

I tried:

#bottom-bar {
   ...the css above and additionally ...
   display: flex;
   flex-direction: row;
   flex-wrap: nowrap;
   justify-content:space-between;
   align-items: center;
}

#chatmsg{
  width: auto;
}
```

But this got me nowhere. Any help is much appreciated.

答:
Using display:flex on the container and by adjusting the input div's flex:1 or setting it's width to 100% should do it. Check output in the console.

FLEX

The input box will stretch between the images as the container div shrinks or expands in size.

#bottom-bar {
   display: flex;
   flex-direction: row;
   flex-wrap: nowrap;
   justify-content:space-between;
   align-items: center;
}

#chatmsg{
  width: 100%;
}

.textentry {
  /* width: 100% */
  flex: 1;
}```
<pre class="snippet-code-html lang-html prettyprint-override"><code><div id="bottom-bar">
  <div class="button-left">
    <img src="https://placeholdit.co//i/40x40">
  </div>
  <div class="textentry">
    <input type="text" id="chatmsg" name="chatmsg">
  </div>
  <div class="button-right">
    <img src="https://placeholdit.co//i/40x40">
  </div>
</div></code>```

Body:
I have a chat web app with a bottom bar consisting of: button, text entry, button:

    <input type="text" id="chatmsg" name="chatmsg">

#bottom-bar {
    position: fixed;
    bottom: 0px;
    height: 40px;
    width: 100%;
}

What I want to achieve: (1) place the left button to the left of the bottom-bar, (2) place the right button to the right of the bottom-bar and (3) have an input field that stretches in the middle using all the space that is available.

I tried:

#bottom-bar {
   ...the css above and additionally ...
   display: flex;
   flex-direction: row;
   flex-wrap: nowrap;
   justify-content:space-between;
   align-items: center;
}

#chatmsg{
  width: auto;
}

But this got me nowhere. Any help is much appreciated.

Body:
Using display:flex on the container and by adjusting the input div's flex:1 or setting it's width to 100% should do it. Check output in the console.

FLEX

The input box will stretch between the images as the container div shrinks or expands in size.

#bottom-bar {
   display: flex;
   flex-direction: row;
   flex-wrap: nowrap;
   justify-content:space-between;
   align-items: center;
}

#chatmsg{
  width: 100%;
}

.textentry {
  /* width: 100% */
  flex: 1;
}

<div id="bottom-bar">
  <div class="button-left">
    <img src="https://placeholdit.co//i/40x40">
  </div>
  <div class="textentry">
    <input type="text" id="chatmsg" name="chatmsg">
  </div>
  <div class="button-right">
    <img src="https://placeholdit.co//i/40x40">
  </div>
</div>


问:
Not able to force index on mysql index
I'm trying to optimize a query, but explain query gives that mysql is not using any index.

This is my query:

```<code>explain
 SELECT t1.*

    FROM crypto_screener.prices as t1 FORCE INDEX (PRIMARY)

    where t1.id = (
      select t2.id
      from (select *
              from
              crypto_screener.prices FORCE INDEX (allfilters)
              where date > '2017-12-07'
              ) as t2
      where t2.symbol = t1.symbol
      order by t2.id desc
      limit 1
      )
;
</code>```

This is show index from prices;

```<code>Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
prices  0   PRIMARY 1   id  A   57718   NULL    NULL        BTREE
prices  0   priceid 1   id  A   57718   NULL    NULL        BTREE
prices  1   pricefilters    1   symbol  A   369 NULL    NULL    YES BTREE
prices  1   pricefilters    2   date    A   57718   NULL    NULL    YES BTREE
prices  1   datefilters 1   date    A   506 NULL    NULL    YES BTREE
prices  1   symbolfilters   1   symbol  A   421 NULL    NULL    YES BTREE
prices  1   allfilters  1   id  A   57718   NULL    NULL        BTREE
prices  1   allfilters  2   symbol  A   57718   NULL    NULL    YES BTREE
prices  1   allfilters  3   date    A   57718   NULL    NULL    YES BTREE
</code>```

The query doesn't run now with the amount of data inside the table. How can I optimize this?

答:
It looks like you want the row with the highest id value for each distinct symbol value within the particular date range.

Here's how you do that.  First, use a subquery to get a list of symbol, id values showing the highest id value for each symbol, for the subset of your table with the desired dates.

```<code>                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol
</code>```

This subquery can be optimized by a compound index on date, symbol, id. Why? It random-accesses the index to the first eligible date, then scans it sequentially looking for distinct values of symbol and id.

You can test that subquery. Make sure it's giving you reasonable results.

Then use it in a main query, like so:

```<code> SELECT t1.*
   FROM prices t1
   JOIN (
                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol
        ) t2 ON t1.id = t2.id AND t1.symbol = t2.symbol
</code>```

Now, because your id values are all unique, you can simplify the ON condition by leaving out AND t1.symbol = t2.symbol.

This should, with the proper compound index, run very quickly indeed.

Pro tip 1: Don't create extra indexes just for good measure. Make sure they are there to help some query you need to run.

Pro tip 2: date > '2017-12-07' excludes rows at midnight on the date you named, but takes all rows after midnight on that date. If the datatype of your date column is DATETIME, using > is probably incorrect. If the datatype is DATE, your filter means the same thing as date >= '2017-12-08'.

Pro tip 3: trying to force MySQL's query planner to use certain indexes is usually counterproductive. Much better is making sure you have the correct indexes.

Title: Not able to force index on mysql index

Body:
I'm trying to optimize a query, but explain query gives that mysql is not using any index.

This is my query:

explain
 SELECT t1.*

    FROM crypto_screener.prices as t1 FORCE INDEX (PRIMARY)

    where t1.id = (
      select t2.id
      from (select *
              from
              crypto_screener.prices FORCE INDEX (allfilters)
              where date > '2017-12-07'
              ) as t2
      where t2.symbol = t1.symbol
      order by t2.id desc
      limit 1
      )
;

This is show index from prices;

Table   Non_unique  Key_name    Seq_in_index    Column_name Collation   Cardinality Sub_part    Packed  Null    Index_type  Comment Index_comment
prices  0   PRIMARY 1   id  A   57718   NULL    NULL        BTREE
prices  0   priceid 1   id  A   57718   NULL    NULL        BTREE
prices  1   pricefilters    1   symbol  A   369 NULL    NULL    YES BTREE
prices  1   pricefilters    2   date    A   57718   NULL    NULL    YES BTREE
prices  1   datefilters 1   date    A   506 NULL    NULL    YES BTREE
prices  1   symbolfilters   1   symbol  A   421 NULL    NULL    YES BTREE
prices  1   allfilters  1   id  A   57718   NULL    NULL        BTREE
prices  1   allfilters  2   symbol  A   57718   NULL    NULL    YES BTREE
prices  1   allfilters  3   date    A   57718   NULL    NULL    YES BTREE

The query doesn't run now with the amount of data inside the table. How can I optimize this?

Body:
It looks like you want the row with the highest id value for each distinct symbol value within the particular date range.

Here's how you do that.  First, use a subquery to get a list of symbol, id values showing the highest id value for each symbol, for the subset of your table with the desired dates.

                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol

This subquery can be optimized by a compound index on date, symbol, id. Why? It random-accesses the index to the first eligible date, then scans it sequentially looking for distinct values of symbol and id.

You can test that subquery. Make sure it's giving you reasonable results.

Then use it in a main query, like so:

 SELECT t1.*
   FROM prices t1
   JOIN (
                 SELECT symbol, MAX(id) id
                   FROM prices
                  WHERE date >  '2017-12-07'
                  GROUP BY symbol
        ) t2 ON t1.id = t2.id AND t1.symbol = t2.symbol

Now, because your id values are all unique, you can simplify the ON condition by leaving out AND t1.symbol = t2.symbol.

This should, with the proper compound index, run very quickly indeed.

Pro tip 1: Don't create extra indexes just for good measure. Make sure they are there to help some query you need to run.

Pro tip 2: date > '2017-12-07' excludes rows at midnight on the date you named, but takes all rows after midnight on that date. If the datatype of your date column is DATETIME, using > is probably incorrect. If the datatype is DATE, your filter means the same thing as date >= '2017-12-08'.

Pro tip 3: trying to force MySQL's query planner to use certain indexes is usually counterproductive. Much better is making sure you have the correct indexes.


问:
Passing a byte[] in Java to a function in C through JNI: how to use jarraybyte
This is the first time that I use the JNI and also the first time that I have to write some lines in C.

What I am trying to do is very simple. I'm just trying to switch the endiannes of a byte[] using a C routine.

In java it is done like this:

```<code>public void switchEndianness(byte[] array){

        byte byte1;
        byte byte2;

        for(int i = 0; i < array.length ; i+=2){
            byte1 = array[i];
            byte2 = array[i+1];

            array[i] = byte2;
            array[i+1] = byte1;
        }
}</code>```

So to do this using JNI, I've tried to imlpement the same routine in the JNICALL, but it doesn't compile. What I've written so far is this:

```<code>JNIEXPORT void JNICALL Java_CEndianness_switchEndianness(JNIEnv *env, jobject obj, jbyteArray array, jint offset, jint length){

    char byte1;
    char byte2;

    int i;
    for(i = offset; i  < length ; i+=2){
        byte1 = array[i];
        byte2 = array[i+1];

        array[i] = byte2;
        array[i+1] = byte1;
    }
}</code>```

I have no clue how to use the jbyteArray type of data. is it possible to store a jbyte in a char??? Another question is.. when this routine is over...will the byte[] in java be modified?? Or is it only modified inside the C call?

Any help???

Thanks to everybody!

答:
qrtt has given you a great answer.

However, the JNI has very comprehensive and (relatively) easy-to-understand documentation that you should read front-to-back if you will be using JNI features again in the future. You can find it here:
http://docs.oracle.com/javase/7/docs/technotes/guides/jni/spec/jniTOC.html

For your particular case, here's the section on dealing with arrays:
http://docs.oracle.com/javase/7/docs/technotes/guides/jni/spec/design.html#wp1265

Body:
This is the first time that I use the JNI and also the first time that I have to write some lines in C.

What I am trying to do is very simple. I'm just trying to switch the endiannes of a byte[] using a C routine.

In java it is done like this:

public void switchEndianness(byte[] array){

        byte byte1;
        byte byte2;

        for(int i = 0; i < array.length ; i+=2){
            byte1 = array[i];
            byte2 = array[i+1];

            array[i] = byte2;
            array[i+1] = byte1;
        }
}

So to do this using JNI, I've tried to imlpement the same routine in the JNICALL, but it doesn't compile. What I've written so far is this:

JNIEXPORT void JNICALL Java_CEndianness_switchEndianness(JNIEnv *env, jobject obj, jbyteArray array, jint offset, jint length){

    char byte1;
    char byte2;

    int i;
    for(i = offset; i  < length ; i+=2){
        byte1 = array[i];
        byte2 = array[i+1];

        array[i] = byte2;
        array[i+1] = byte1;
    }
}

I have no clue how to use the jbyteArray type of data. is it possible to store a jbyte in a char??? Another question is.. when this routine is over...will the byte[] in java be modified?? Or is it only modified inside the C call?

Any help???

Thanks to everybody!


问:
Processing of kivy canvas texture in memory
I want to pass pixels from the kivy canvas to keras model (neural network). Simple approach would be to export the image to png and then read it using scikit-image or PIL. Question is can this be done in memory without such redundant steps?

This is what I came up with:

```<code>fbo = Fbo()
fbo.add(self.canvas)
fbo.draw()
img = Image.frombytes('RGBA', img_size, fbo.pixels)
</code>```

In order to check if everything works, img was saved as png. Unfortunately it turned out to be complete mess. My question is how can I get it to work properly?

Here is code that reproduces/demonstrates this problem:

```<code>from PIL import Image
from kivy.app import App
from kivy.uix.widget import Widget
from kivy.uix.button import Button
from kivy.graphics import Line, Fbo
from kivy.uix.floatlayout import FloatLayout

class Painter(Widget):

    def on_touch_down(self, touch):
        with self.canvas:
            touch.ud['line'] = Line(points=(touch.x, touch.y), width=50)

    def on_touch_move(self, touch):
        touch.ud['line'].points += [touch.x, touch.y]

    def save(self, obj):

        fbo = Fbo()
        fbo.add(self.canvas)
        fbo.draw()
        img = Image.frombytes('RGBA', self.size, fbo.pixels)
        img.save('img.png')

class App(App):

    def build(self):
        layout = FloatLayout()
        painter = Painter()
        btn = Button(text='Save', on_release=painter.save, size_hint=(0.2,0.2))

        layout.add_widget(painter)
        layout.add_widget(btn)
        return layout

App().run()
</code>```

答:
Every kivy widget has a export_to_png method which allow to save a png image of the canvas of this widget so instead of using Fbo and PIL Image you could use it.

Something like this:

```<code>...

class Painter(Widget):

    ...

    def save(self, *args):
        self.export_to_png(filename='img')

    ...
...
</code>```

Body:
I want to pass pixels from the kivy canvas to keras model (neural network). Simple approach would be to export the image to png and then read it using scikit-image or PIL. Question is can this be done in memory without such redundant steps?

This is what I came up with:

fbo = Fbo()
fbo.add(self.canvas)
fbo.draw()
img = Image.frombytes('RGBA', img_size, fbo.pixels)

In order to check if everything works, img was saved as png. Unfortunately it turned out to be complete mess. My question is how can I get it to work properly?

Here is code that reproduces/demonstrates this problem:

from PIL import Image
from kivy.app import App
from kivy.uix.widget import Widget
from kivy.uix.button import Button
from kivy.graphics import Line, Fbo
from kivy.uix.floatlayout import FloatLayout

class Painter(Widget):

    def on_touch_down(self, touch):
        with self.canvas:
            touch.ud['line'] = Line(points=(touch.x, touch.y), width=50)

    def on_touch_move(self, touch):
        touch.ud['line'].points += [touch.x, touch.y]

    def save(self, obj):

        fbo = Fbo()
        fbo.add(self.canvas)
        fbo.draw()
        img = Image.frombytes('RGBA', self.size, fbo.pixels)
        img.save('img.png')

class App(App):

    def build(self):
        layout = FloatLayout()
        painter = Painter()
        btn = Button(text='Save', on_release=painter.save, size_hint=(0.2,0.2))

        layout.add_widget(painter)
        layout.add_widget(btn)
        return layout

App().run()

Body:
Every kivy widget has a export_to_png method which allow to save a png image of the canvas of this widget so instead of using Fbo and PIL Image you could use it.

Something like this:

...

class Painter(Widget):

    ...

    def save(self, *args):
        self.export_to_png(filename='img')

    ...
...


问:
Copy sheets from different workbooks into a single workbook
I'm trying to copy the first sheet from multiple workbooks into a new workbook, on separate sheets, and summarise the data in one more sheet at the end of the workbook.

At the moment my workbook creation isn't really working. It copies the first sheet into a new workbook but then stops working:

error 9, subscript out of range

```<code>Sub MergeSelectedWorkbooks()
    Dim SummaryBook As Workbook
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Set SummaryBook = Workbooks.Add(xlWBATWorksheet)

    FolderPath = "Path here"
    ChDrive FolderPath
    ChDir FolderPath
    SelectedFiles = Application.GetOpenFilename( _
       filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    Nrow = 1
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        Set SummarySheet = Workbook.Worksheets(NRow) 'this is what seems to be the reason it doesn't work

        Set WorkBk = Workbooks.Open(FileName)
        Application.Run WorkBk.Name & "!Macro" 'this runs a macro in the workbook that I want to copy the sheet from
        Set SourceRange = WorkBk.Worksheets(1).Range("A1:G21")
        Set DestRange = SummarySheet.Range("A1:G21")
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        DestRange.Value = SourceRange.Value
        NRow = NRow + 1
        WorkBk.Close savechanges:=False
    Next NFile

    SummarySheet.Columns.AutoFit
End Sub
</code>```

Any ideas?

答:
Here you go.

```<code>Sub Basic_Example_1()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long

    'Fill in the path\folder where the files are
    MyPath = "C:\Users\Ron\test"

    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next

                With mybook.Worksheets(1)
                    Set sourceRange = .Range("A1:C1")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(Fnum)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If

        Next Fnum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub
</code>```

https://www.rondebruin.nl/win/s3/win008.htm

Body:
I'm trying to copy the first sheet from multiple workbooks into a new workbook, on separate sheets, and summarise the data in one more sheet at the end of the workbook.

At the moment my workbook creation isn't really working. It copies the first sheet into a new workbook but then stops working:

error 9, subscript out of range

Sub MergeSelectedWorkbooks()
    Dim SummaryBook As Workbook
    Dim SummarySheet As Worksheet
    Dim FolderPath As String
    Dim SelectedFiles() As Variant
    Dim NRow As Long
    Dim FileName As String
    Dim NFile As Long
    Dim WorkBk As Workbook
    Dim SourceRange As Range
    Dim DestRange As Range
    Set SummaryBook = Workbooks.Add(xlWBATWorksheet)

    FolderPath = "Path here"
    ChDrive FolderPath
    ChDir FolderPath
    SelectedFiles = Application.GetOpenFilename( _
       filefilter:="Excel Files (*.xl*), *.xl*", MultiSelect:=True)
    Nrow = 1
    For NFile = LBound(SelectedFiles) To UBound(SelectedFiles)
        FileName = SelectedFiles(NFile)
        Set SummarySheet = Workbook.Worksheets(NRow) 'this is what seems to be the reason it doesn't work

        Set WorkBk = Workbooks.Open(FileName)
        Application.Run WorkBk.Name & "!Macro" 'this runs a macro in the workbook that I want to copy the sheet from
        Set SourceRange = WorkBk.Worksheets(1).Range("A1:G21")
        Set DestRange = SummarySheet.Range("A1:G21")
        Set DestRange = DestRange.Resize(SourceRange.Rows.Count, _
           SourceRange.Columns.Count)

        DestRange.Value = SourceRange.Value
        NRow = NRow + 1
        WorkBk.Close savechanges:=False
    Next NFile

    SummarySheet.Columns.AutoFit
End Sub

Any ideas?

Body:
Here you go.

Sub Basic_Example_1()
    Dim MyPath As String, FilesInPath As String
    Dim MyFiles() As String
    Dim SourceRcount As Long, Fnum As Long
    Dim mybook As Workbook, BaseWks As Worksheet
    Dim sourceRange As Range, destrange As Range
    Dim rnum As Long, CalcMode As Long

    'Fill in the path\folder where the files are
    MyPath = "C:\Users\Ron\test"

    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If

    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xl*")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If

    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop

    'Change ScreenUpdating, Calculation and EnableEvents
    With Application
        CalcMode = .Calculation
        .Calculation = xlCalculationManual
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    'Add a new workbook with one sheet
    Set BaseWks = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    rnum = 1

    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
            Set mybook = Nothing
            On Error Resume Next
            Set mybook = Workbooks.Open(MyPath & MyFiles(Fnum))
            On Error GoTo 0

            If Not mybook Is Nothing Then

                On Error Resume Next

                With mybook.Worksheets(1)
                    Set sourceRange = .Range("A1:C1")
                End With

                If Err.Number > 0 Then
                    Err.Clear
                    Set sourceRange = Nothing
                Else
                    'if SourceRange use all columns then skip this file
                    If sourceRange.Columns.Count >= BaseWks.Columns.Count Then
                        Set sourceRange = Nothing
                    End If
                End If
                On Error GoTo 0

                If Not sourceRange Is Nothing Then

                    SourceRcount = sourceRange.Rows.Count

                    If rnum + SourceRcount >= BaseWks.Rows.Count Then
                        MsgBox "Sorry there are not enough rows in the sheet"
                        BaseWks.Columns.AutoFit
                        mybook.Close savechanges:=False
                        GoTo ExitTheSub
                    Else

                        'Copy the file name in column A
                        With sourceRange
                            BaseWks.cells(rnum, "A"). _
                                    Resize(.Rows.Count).Value = MyFiles(Fnum)
                        End With

                        'Set the destrange
                        Set destrange = BaseWks.Range("B" & rnum)

                        'we copy the values from the sourceRange to the destrange
                        With sourceRange
                            Set destrange = destrange. _
                                            Resize(.Rows.Count, .Columns.Count)
                        End With
                        destrange.Value = sourceRange.Value

                        rnum = rnum + SourceRcount
                    End If
                End If
                mybook.Close savechanges:=False
            End If

        Next Fnum
        BaseWks.Columns.AutoFit
    End If

ExitTheSub:
    'Restore ScreenUpdating, Calculation and EnableEvents
    With Application
        .ScreenUpdating = True
        .EnableEvents = True
        .Calculation = CalcMode
    End With
End Sub

https://www.rondebruin.nl/win/s3/win008.htm


问:
how to setup and test if a location is using the closest rds read replica? aws
I am currently learning how rds replica works in aws.
I got everything setup and now I have a postgres rds instance in the origin of oregon which is the original rds instance.  Then I created one cross origin read replica which is in the origin of tokyo.  How do I setup and test if people nearby tokyo is actually access the read replica from tokyo instead of accessing the original rds instance in oregon?

答:
Each RDS Read Replica has a DNS endpoint. You could combine DNS Server Geolocation, such as provided by Route 53, to provide the endpoint closest to the customer's location.

To test and verify this, you would need to enable logging for your RDS instance and then manually map the customer's IP address -> region -> read replica to verify.


问:
Using JWT claims vs. storing data about user in a plain text cookie
Our intranet system stores user data once the user logs in simply using a cookie (UserID, Name, Role..)

I was learning about JWT and tokens today and wondering is there any advantage to using that over the current way?

It seems like something might be insecure storing plain text in cookies but I also read that other web sites cannot see those cookies..

So is there any strong reason to use JWT tokens instead ?

答:
A cookie can not be accesed by a different domain that the one which created it. This restriction is called "same-origin" policy and is a security measure to protect the local data of the site, but it does not mean that your cookie is conveniently secured

Since you are not validating the cookie content in server side, for example a user could change his UserId or Role to get access to unauthorized resources.

JWT can help you because thencontent is signed with a secret key. Any alteration to the content will break the digital signature and the server will reject the token.

An alternative is to use server session and store this data on server.
The drawback with respect to JWT is that it needs server storage.


问:
Graphics DrawString Not Showing Up in JPanel
I'm trying to make a Java program that allows you to program simple games using drag-and-drop code panels. Using a JTextField in my code panel created a glitch where there would be a long delay before the size updated, so I resorted to making my own JTextField using a JPanel.

Here is the code:

```<code>package editor;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Graphics;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;

import javax.swing.JPanel;
import javax.swing.border.LineBorder;

public class JCustomTextField extends JPanel {

    String text;
    int xMargin;
    int yMargin;
    int width;
    int height;

    public JCustomTextField(String initText, int xMargin, int yMargin, int height, int width) {

        this.xMargin = xMargin;
        this.yMargin = yMargin;
        this.width = width;
        this.height = height;
        this.text = initText;

        setBorder(new LineBorder(Color.DARK_GRAY, 2));
        setBounds(0, 0, width, height);

        addKeyListener(new KeyListener() {

            @Override
            public void keyPressed(KeyEvent arg0) {
                text.concat("" + arg0.getKeyChar());
                repaint();
            }

            @Override
            public void keyReleased(KeyEvent arg0) {

            }

            @Override
            public void keyTyped(KeyEvent arg0) {

            }

        });
    }

    @Override
    public void paintComponent(Graphics g) {
        super.paintComponent(g);
        g.setColor(Color.WHITE);
        g.drawString(text, xMargin, yMargin);
    }
    public Dimension getMinimumSize() {
        return new Dimension(width, height);
    }
    public Dimension getPreferredSize() {
        return new Dimension(width, height);
    }
    public Dimension getMaximumSize() {
        return new Dimension(width, height);
    }

}
</code>```

Now I create a JComponent array containing a JLabel and the text panel and add it to the code panel.

```<code>JComponent[] jumpComps = {new JLabel("Jump Height:"), new JCustomTextField("10", 2, 2, 20, 50)};
    CodePanel jumpPanel = new CodePanel(0,0, jumpComps, 1);
</code>```

The border shows up, but not the text.

Here's a picture of the code panel:

I am using the Substance Graphite Glass Look and Feel.

答:
From the KeyEvent documentation:

The getKeyChar method always returns a valid Unicode character or CHAR_UNDEFINED. Character input is reported by KEY_TYPED events: KEY_PRESSED and KEY_RELEASED events are not necessarily associated with character input. Therefore, the result of the getKeyChar method is guaranteed to be meaningful only for KEY_TYPED events.

For key pressed and key released events, the getKeyCode method returns the event's keyCode. For key typed events, the getKeyCode method always returns VK_UNDEFINED.

getKeyChar is valid only for keyTyped events.  Its return value is undefined for keyPressed and keyReleased events.

All that said, a text field is much more complex than this.  You aren’t handling cursor rendering, backspacing, arrow keys, selection, or accessibility.  You’re better off using a real JTextField and properly addressing your layout problem.

Body:
I'm trying to make a Java program that allows you to program simple games using drag-and-drop code panels. Using a JTextField in my code panel created a glitch where there would be a long delay before the size updated, so I resorted to making my own JTextField using a JPanel.

Here is the code:

package editor;

import java.awt.Color;
import java.awt.Dimension;
import java.awt.Graphics;
import java.awt.event.KeyEvent;
import java.awt.event.KeyListener;

import javax.swing.JPanel;
import javax.swing.border.LineBorder;

public class JCustomTextField extends JPanel {

    String text;
    int xMargin;
    int yMargin;
    int width;
    int height;

    public JCustomTextField(String initText, int xMargin, int yMargin, int height, int width) {

        this.xMargin = xMargin;
        this.yMargin = yMargin;
        this.width = width;
        this.height = height;
        this.text = initText;

        setBorder(new LineBorder(Color.DARK_GRAY, 2));
        setBounds(0, 0, width, height);

        addKeyListener(new KeyListener() {

            @Override
            public void keyPressed(KeyEvent arg0) {
                text.concat("" + arg0.getKeyChar());
                repaint();
            }

            @Override
            public void keyReleased(KeyEvent arg0) {

            }

            @Override
            public void keyTyped(KeyEvent arg0) {

            }

        });
    }

    @Override
    public void paintComponent(Graphics g) {
        super.paintComponent(g);
        g.setColor(Color.WHITE);
        g.drawString(text, xMargin, yMargin);
    }
    public Dimension getMinimumSize() {
        return new Dimension(width, height);
    }
    public Dimension getPreferredSize() {
        return new Dimension(width, height);
    }
    public Dimension getMaximumSize() {
        return new Dimension(width, height);
    }

}

Now I create a JComponent array containing a JLabel and the text panel and add it to the code panel.

JComponent[] jumpComps = {new JLabel("Jump Height:"), new JCustomTextField("10", 2, 2, 20, 50)};
    CodePanel jumpPanel = new CodePanel(0,0, jumpComps, 1);

The border shows up, but not the text.

Here's a picture of the code panel:

I am using the Substance Graphite Glass Look and Feel.


问:
What Index should be created for this query?
So I need help to figured it out what Index(es) I need to create to improve the performance of this query:

```<code>SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F, produto P
WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta';
</code>```

The tables fornecedor and produto are created this way:

Fornecedor

```<code>create table fornecedor (
nif char(9) not null unique,
nomef varchar(80) not null,
constraint nif_size CHECK (Length(nif)=9),
constraint pk_fornecedor primary key(nif));
</code>```

Produto

```<code>create table produto (
ean char(13) not null unique,
design varchar(255) not null,
categoria varchar(80) not null,
forn_primario char(9) not null,
data date not null,
constraint ean_size CHECK (Length(ean)=13),
constraint pk_produto primary key(ean),
constraint fk_produto_categoria foreign key(categoria) references categoria(nome) ON DELETE CASCADE,
constraint fk_produto_fornecedor foreign key(forn_primario) references fornecedor(nif));
</code>```

I dont know if I need to create a Multiple-Key Index like:

```<code>CREATE INDEX multiple_idx ON produto (forn_primario,categoria);
</code>```

or something else???

答:
You definitely need an index starting with categoria. The best will be with forn_primario:

```<code> create index ix_producto_categoria on producto(categoria, forn_primario)
</code>```

to further support your query you may want to create

```<code> create index ix_fornecedor_nif on fornecedor(nif, nomef)
</code>```

Body:
So I need help to figured it out what Index(es) I need to create to improve the performance of this query:

SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F, produto P
WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta';

The tables fornecedor and produto are created this way:

Fornecedor

create table fornecedor (
nif char(9) not null unique,
nomef varchar(80) not null,
constraint nif_size CHECK (Length(nif)=9),
constraint pk_fornecedor primary key(nif));

Produto

create table produto (
ean char(13) not null unique,
design varchar(255) not null,
categoria varchar(80) not null,
forn_primario char(9) not null,
data date not null,
constraint ean_size CHECK (Length(ean)=13),
constraint pk_produto primary key(ean),
constraint fk_produto_categoria foreign key(categoria) references categoria(nome) ON DELETE CASCADE,
constraint fk_produto_fornecedor foreign key(forn_primario) references fornecedor(nif));

I dont know if I need to create a Multiple-Key Index like:

CREATE INDEX multiple_idx ON produto (forn_primario,categoria);

or something else???

Body:
You definitely need an index starting with categoria. The best will be with forn_primario:

 create index ix_producto_categoria on producto(categoria, forn_primario)

to further support your query you may want to create

 create index ix_fornecedor_nif on fornecedor(nif, nomef)


问:
Most convenient way to perform an HTTP request in a series of commands (Swift)
I have a view with some text fields. When the user clicks "Finish" I verify that each field is not empty. Two of those fields are an email address and a username field. I want to verify that the email address and username aren't already in use in my database.

I've built an HTTP request that performs the checks, but the rest of the code executes before I get a response back.

```<code>if username.text == "" {
    // Alert user
    return
}

if email.text == "" {
    // Alert user
    return
}

let server = ServerController() // Custom HTTP request object I made

server.DoesUsernameExist(username: username.text!)
server.DoesEmailExist(email: email.text!)

// Properties server.usernameExists and server.emailExists begin as nil and are set to true or false with server.DoesUsernameExist() and server.DoesEmailExist()

if server.usernameExists == true {
    // Alert user
    return
}

if server.emailExists== true {
    // Alert user
    return
}

server.CreateProfile(/* parameters */) // Creates a new entry in the database
</code>```

But the properties server.usernameExists and server.emailExists are still nil when server.CreateProfile() is executed.

I would love something as simple as this:

```<code>let emailExists = server.DoesEmailExist(email: email.text!)

if emailExists == true {
    // Alert user
    return
}
</code>```

But not having any luck with that. I'm new to doing HTTP requests in Swift.

Any advice would be welcomed. Thanks!

答:
Unfortunately the code that would be THAT simple is technically impossible. You can't simply write that because call to a server is async operation (it happens over time, not instantly like a = 2+2 operation for example). Therefore your best bet is to use callbacks that would trigger after server responds to a request. It would be something within the lines:

after user types in the name, you start API call to server and add
flag that marks that it's in progress.

If response to this call is received before user submits registration -> you update the UI accordingly by showing the message that it's not unique

If response is not received by the time user presses submit, you disable ALL UI elements, display activity indicator and wait for the response of that call

(3a) If response when received says it's unique username
you proceed to with registration, otherwise you reenable UI and
provide user with the feedback that username exists.

Alternatively, you could split this into couple of steps, where after user enters the name, he has to press the button, after which you disable the UI and wait for the server response. If that is successful you proceed to step #2, etc. Hope this helps.

Body:
I have a view with some text fields. When the user clicks "Finish" I verify that each field is not empty. Two of those fields are an email address and a username field. I want to verify that the email address and username aren't already in use in my database.

I've built an HTTP request that performs the checks, but the rest of the code executes before I get a response back.

if username.text == "" {
    // Alert user
    return
}

if email.text == "" {
    // Alert user
    return
}

let server = ServerController() // Custom HTTP request object I made

server.DoesUsernameExist(username: username.text!)
server.DoesEmailExist(email: email.text!)

// Properties server.usernameExists and server.emailExists begin as nil and are set to true or false with server.DoesUsernameExist() and server.DoesEmailExist()

if server.usernameExists == true {
    // Alert user
    return
}

if server.emailExists== true {
    // Alert user
    return
}

server.CreateProfile(/* parameters */) // Creates a new entry in the database

But the properties server.usernameExists and server.emailExists are still nil when server.CreateProfile() is executed.

I would love something as simple as this:

let emailExists = server.DoesEmailExist(email: email.text!)

if emailExists == true {
    // Alert user
    return
}

But not having any luck with that. I'm new to doing HTTP requests in Swift.

Any advice would be welcomed. Thanks!


问:
Navicat MySQL - Import feature screwing up my numbers
I've been wrestling with this problem for quite a while now.

A client has given me 4 spreadsheet files, all of which are just fine. Not a thing wrong with them.

They just have stuff like Part Numbers, Item names, and Prices.

Prices look something like this: 7.99 or 0.58

But when I import the spreadsheets into a new, empty database table using Navicat for MySQL, the numbers get screwed up!

Most of them look like this now: 7.9986 and 0.0580 etc etc etc!

And because there are tens of thousands of prices, and because I don't know the pricing for all of their products, I don't know sometimes which is the real price, and which is a wrong one.

I have no idea what's causing this problem.

Any help at all is greatly appreciated!

Thank you

答: This sounds like it could be a precision problem.  What data type are you using for the price column in the "new, empty" table?  Try using DECIMAL or NUMERIC rather than (say) FLOAT, if applicable.</p>


答:
Your query is better written as:

```<code>SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F JOIN
     produto P
     ON F.nif = P.forn_primario AND P.categoria = 'Fruta';
</code>```

For this version, you want indexes on product(categoria, forn_primario) and fornecdor(nif).

Then, if nif/nomef is unique in the first table, it should be expressed as:

```<code>SELECT F.nif, F.nomef
FROM fornecedor F
WHERE EXISTS (SELECT 1
              FROM produto P
              WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta'
             );
</code>```

For this version, you want an index on produto(forn_primario, categoria).

Title: What Index should be created for this query?

Body:
Your query is better written as:

SELECT DISTINCT F.nif, F.nomef
FROM fornecedor F JOIN
     produto P
     ON F.nif = P.forn_primario AND P.categoria = 'Fruta';

For this version, you want indexes on product(categoria, forn_primario) and fornecdor(nif).

Then, if nif/nomef is unique in the first table, it should be expressed as:

SELECT F.nif, F.nomef
FROM fornecedor F
WHERE EXISTS (SELECT 1
              FROM produto P
              WHERE F.nif = P.forn_primario AND P.categoria = 'Fruta'
             );

For this version, you want an index on produto(forn_primario, categoria).


问:
asp.net post request form empty
I'm using angularJS + asp.net for post request but the Request.Form is always empty. Here is my code

Index.cshtml

```<code><div ng-app="phone" ng-controller="phoneCtrl">
    <input type="text" ng-model="phoneNumber">
    <button ng-click="sendSMS()"> Submit </button>
    <br>
    {{response}}
    {{error}}
</div>

@section scripts {
    <script>
        var app = angular.module('phone', []);
        app.controller('phoneCtrl', function ($scope, $http) {
            $scope.sendSMS = function () {
                var params = {
                    phone : $scope.phoneNumber
                };

                var config = {
                    headers: {
                        'Content-Type': 'application/x-www-form-urlencoded;charset=utf-8'
                    }
                };

                $http.post('Home/sendSMS',params , config)
                .then(function (response, status, headers, config) {
                    $scope.response = response.data;
                })
                .catch(function (data, status, headers, config) {
                    $scope.error = data;
                });
            };
        });
    </script>
}
</code>```

HomeController.cs

```<code>[HttpPost]
public IActionResult SendSMS()
{
    System.Diagnostics.Debug.WriteLine("The form has " + Request.Form);
    return Content("Received");
}
</code>```

The Request.Form is always empty but I can see a phone entry in the request body of the post request so I'm wondering if I'm extracting the data wrong in .net

答:
Would you try to change Content-Type to Json.

```<code>            var config = {
                headers: {
                    'Content-Type': 'application/json;'
                }
            };
</code>```

Title: asp.net post request form empty

Body:
I'm using angularJS + asp.net for post request but the Request.Form is always empty. Here is my code

Index.cshtml

    <input type="text" ng-model="phoneNumber">
    <button ng-click="sendSMS()"> Submit </button>

    {{response}}
    {{error}}

@section scripts {

}

HomeController.cs

[HttpPost]
public IActionResult SendSMS()
{
    System.Diagnostics.Debug.WriteLine("The form has " + Request.Form);
    return Content("Received");
}

The Request.Form is always empty but I can see a phone entry in the request body of the post request so I'm wondering if I'm extracting the data wrong in .net

Body:
Would you try to change Content-Type to Json.

            var config = {
                headers: {
                    'Content-Type': 'application/json;'
                }
            };


问:
Attempt number over transitory statuses in T-SQL
I have a table that looks like this (without the attempt column):

```<code>Account      Status        Date         Attempt(*)
</code>```

```<code>A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   1
A            SUCCESS       15/12/2017   2
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   1
C            ERROR         14/12/2017   2
C            IN_PROGRESS   15/12/2017   3
</code>```

I want to get the Attempt(*) column. The ERROR and SUCCESS states are end states. For example, for Account C, we have that the first Attempt produced an ERROR, the second attempt produced an ERROR and the third attempt is IN_PROGRESS.

I know that RANK() alone won't work due to the transitory statuses 'IN_PROGRESS'. For example, if I was to go:

RANK() OVER(PARTITION BY Account ORDER BY Date ASC)

I would get:

```<code>Account      Status        Date         Attempt(*)
</code>```

```<code>A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   2
A            SUCCESS       15/12/2017   3
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   2
C            ERROR         14/12/2017   3
C            IN_PROGRESS   15/12/2017   4
</code>```

Which does not consider the transitory statuses. I would use the result in the second table to create a new column to get the desired result, the sudo code would go something like:

New_Attempt[n] = if Attempt(* ) = 1 then 1 else (if Attempt(* ) > 1 and Status[n-1] in(ERROR, SUCCESS) then Attempt[n-1] + 1 else Attempt[n-1])

where 'n' is the row number.

I am relatively new to SQL, I have researched other ranking functions like NTILE and DENSE_RANK, but I still can't figure out a way to do this.

P.S. I am using T-SQL in SQL Server Management Studio 2008

Thanks! :)

答:
This is not the prettiest query in the world but it will get you the result I think you want. Basically I just followed the rule of IN_PROGRESS ends the counting of attempts. I added a few more records to the sample set to prove out the functionality. I would also like to note that this solution will probably not scale well. So if your data set is large beware.</p>

```<code>CREATE TABLE #Test (Account char(1), [Status] varchar(50), [Date] datetime)

INSERT INTO #Test (Account, [Status], [Date])VALUES
('A',   'IN_PROGRESS',   '12/12/2017'),
('A',   'ERROR',         '12/13/2017'),
('A',   'SUCCESS',       '12/15/2017'),
('A',   'IN_PROGRESS',   '12/16/2017'),
('A',   'IN_PROGRESS',   '12/17/2017'),
('B',   'ERROR',         '12/10/2017'),
('B',   'SUCCESS',       '12/13/2017'),
('B',   'IN_PROGRESS',   '12/15/2017'),
('B',   'IN_PROGRESS',   '12/17/2017'),
('C',   'IN_PROGRESS',   '12/11/2017'),
('C',   'ERROR',         '12/13/2017'),
('C',   'ERROR',         '12/14/2017'),
('C',   'IN_PROGRESS',   '12/15/2017'),
('C',   'ERROR',         '12/16/2017'),
('C',   'ERROR',         '12/17/2017')

SELECT
    T1.Account,
    T1.[Status],
    T1.[Date],
    1 + SUM(COALESCE(TC.Attempt, 0)) AS 'Attempt(*)'
FROM
    #Test AS T1
    OUTER APPLY
    (
        SELECT
            1 AS 'Attempt'
        FROM
            #Test AS T2
        WHERE
            T2.Account = T1.Account
            AND T2.[Date] < T1.[Date]
            AND T2.[Date] > COALESCE((SELECT TOP 1 T3.[Date] FROM #Test AS T3 WHERE T3.Account = T1.Account AND T3.[Status] = 'IN_PROGRESS' AND T3.[Date] < T1.[Date] ORDER BY T3.[Date] DESC), '01/01/2017')
            AND T2.Status <> 'IN_PROGRESS'
    ) AS TC
GROUP BY
    T1.Account,
    T1.[Status],
    T1.[Date]
ORDER BY
    T1.Account,
    T1.[Date]

DROP TABLE #Test
</code>```

Body:
I have a table that looks like this (without the attempt column):

Account      Status        Date         Attempt(*)

A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   1
A            SUCCESS       15/12/2017   2
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   1
C            ERROR         14/12/2017   2
C            IN_PROGRESS   15/12/2017   3

I want to get the Attempt(*) column. The ERROR and SUCCESS states are end states. For example, for Account C, we have that the first Attempt produced an ERROR, the second attempt produced an ERROR and the third attempt is IN_PROGRESS.

I know that RANK() alone won't work due to the transitory statuses 'IN_PROGRESS'. For example, if I was to go:

RANK() OVER(PARTITION BY Account ORDER BY Date ASC)

I would get:

Account      Status        Date         Attempt(*)

A            In_PROGRESS   12/12/2017   1
A            ERROR         13/12/2017   2
A            SUCCESS       15/12/2017   3
B            ERROR         10/12/2017   1
B            SUCCESS       13/12/2017   2
C            IN_PROGRESS   11/12/2017   1
C            ERROR         13/12/2017   2
C            ERROR         14/12/2017   3
C            IN_PROGRESS   15/12/2017   4

Which does not consider the transitory statuses. I would use the result in the second table to create a new column to get the desired result, the sudo code would go something like:

New_Attempt[n] = if Attempt(* ) = 1 then 1 else (if Attempt(* ) > 1 and Status[n-1] in(ERROR, SUCCESS) then Attempt[n-1] + 1 else Attempt[n-1])

where 'n' is the row number.

I am relatively new to SQL, I have researched other ranking functions like NTILE and DENSE_RANK, but I still can't figure out a way to do this.

P.S. I am using T-SQL in SQL Server Management Studio 2008

Thanks! :)

Body:
This is not the prettiest query in the world but it will get you the result I think you want. Basically I just followed the rule of IN_PROGRESS ends the counting of attempts. I added a few more records to the sample set to prove out the functionality. I would also like to note that this solution will probably not scale well. So if your data set is large beware.

CREATE TABLE #Test (Account char(1), [Status] varchar(50), [Date] datetime)

INSERT INTO #Test (Account, [Status], [Date])VALUES
('A',   'IN_PROGRESS',   '12/12/2017'),
('A',   'ERROR',         '12/13/2017'),
('A',   'SUCCESS',       '12/15/2017'),
('A',   'IN_PROGRESS',   '12/16/2017'),
('A',   'IN_PROGRESS',   '12/17/2017'),
('B',   'ERROR',         '12/10/2017'),
('B',   'SUCCESS',       '12/13/2017'),
('B',   'IN_PROGRESS',   '12/15/2017'),
('B',   'IN_PROGRESS',   '12/17/2017'),
('C',   'IN_PROGRESS',   '12/11/2017'),
('C',   'ERROR',         '12/13/2017'),
('C',   'ERROR',         '12/14/2017'),
('C',   'IN_PROGRESS',   '12/15/2017'),
('C',   'ERROR',         '12/16/2017'),
('C',   'ERROR',         '12/17/2017')

SELECT
    T1.Account,
    T1.[Status],
    T1.[Date],
    1 + SUM(COALESCE(TC.Attempt, 0)) AS 'Attempt(*)'
FROM
    #Test AS T1
    OUTER APPLY
    (
        SELECT
            1 AS 'Attempt'
        FROM
            #Test AS T2
        WHERE
            T2.Account = T1.Account
            AND T2.[Date] < T1.[Date]
            AND T2.[Date] > COALESCE((SELECT TOP 1 T3.[Date] FROM #Test AS T3 WHERE T3.Account = T1.Account AND T3.[Status] = 'IN_PROGRESS' AND T3.[Date] < T1.[Date] ORDER BY T3.[Date] DESC), '01/01/2017')
            AND T2.Status <> 'IN_PROGRESS'
    ) AS TC
GROUP BY
    T1.Account,
    T1.[Status],
    T1.[Date]
ORDER BY
    T1.Account,
    T1.[Date]

DROP TABLE #Test


问:
I published my private SSH key in a repository
This may be the most stupid question in this forum. But I just created a private repository and somehow my public and private ssh key were written into files inside the local repo.

When I published my repo to bitbucket the files with both of my SSH Keys got published, too. So now they are openly accessable on my repo. And even when I delete them you find them in the log files. How can I safely return from that mistake?

答:
There is no "return" - that private key is now compromised.
 Delete your private key from ~/.ssh, and generate yourself a new one (with ssh-keygen).1

Yes, that will be a pain, because you'll have to re-register your public key with a bunch of stuff.  But there's really no secure alternative.

1. And then figure out how you possibly ended up adding your key to a repo in the first place!