问:
Improve Efficiency of Pandas Transformation Process
Right now I've got a process to extract a large amount of data (~1.5m rows) from a URL that comes in an unorganized way that I need to subsequently regorganize. The current process works flawlessly but it is extremely memory heavy and inefficient so I was looking for help.

The data that I receive comes in the following structure: (notice after exit 8 there are another 5 columns of Na's and None signifying end of current SCP

```<code>['C/A','UNIT','SCP','DATE1','TIME1','DESC1','ENTRIES1','EXITS1','DATE2','TIME2','ESC2',\
 'ENTRIES2','EXITS2','DATE3','TIME3','DESC3','ENTRIES3','EXITS3','DATE4','TIME4','DESC4',\
 'ENTRIES4','EXITS4','DATE5','TIME5','DESC5','ENTRIES5','EXITS5','DATE6','TIME6','DESC6',\
 'ENTRIES6','EXITS6','DATE7','TIME7','DESC7','ENTRIES7','EXITS7','DATE8','TIME8','DESC8',\
 'ENTRIES8','EXITS8']
</code>```

My goal is to have it reorganized like such:

```<code>['c/a','unit','scp','date','time','description','entries','exit']
</code>```

Example of Raw Output:

```<code>     C/A        UNIT    SCP     DATE1   TIME1   DESC1   ENTRIES1    EXITS1  DATE2   TIME2   ESC2    ENTRIES2    EXITS2  DATE3   TIME3   DESC3   ENTRIES3    EXITS3  DATE4   TIME4   DESC4   ENTRIES4    EXITS4  DATE5   TIME5   DESC5   ENTRIES5    EXITS5  DATE6   TIME6   DESC6   ENTRIES6    EXITS6  DATE7   TIME7   DESC7   ENTRIES7    EXITS7  DATE8   TIME8   DESC8   ENTRIES8    EXITS8
0   A002    R051    02-00-00    04-20-13    00:00:00    REGULAR 4084276 1405308 04-20-13    04:00:00    REGULAR 4084308.0   1405312.0   04-20-13    08:00:00    REGULAR 4084332.0   1405348.0   04-20-13    12:00:00    REGULAR 4084429.0   1405441.0   04-20-13    16:00:00    REGULAR 4084714.0   1405494.0   04-20-13    20:00:00    REGULAR 4085107.0   1405550.0   04-21-13    00:00:00    REGULAR 4085286.0   1405578.0   04-21-13    04:00:00    REGULAR 4085317.0   1405582.0
1   A002    R051    02-00-00    04-21-13    08:00:00    REGULAR 4085336 1405603 04-21-13    12:00:00    REGULAR 4085421.0   1405673.0   04-21-13    16:00:00    REGULAR 4085543.0   1405725.0   04-21-13    20:00:00    REGULAR 4085543.0   1405781.0   04-22-13    00:00:00    REGULAR 4085669.0   1405820.0   04-22-13    04:00:00    REGULAR 4085684.0   1405825.0   04-22-13    08:00:00    REGULAR 4085715.0   1405929.0   04-22-13    12:00:00    REGULAR 4085878.0   1406175.0
2   A002    R051    02-00-00    04-22-13    16:00:00    REGULAR 4086116 1406242 04-22-13    20:00:00    REGULAR 4086986.0   1406310.0   04-23-13    00:00:00    REGULAR 4087164.0   1406335.0   04-23-13    04:00:00    REGULAR 4087172.0   1406339.0   04-23-13    08:00:00    REGULAR 4087214.0   1406441.0   04-23-13    12:00:00    REGULAR 4087390.0   1406685.0   04-23-13    16:00:00    REGULAR 4087738.0   1406741.0   04-23-13    20:00:00    REGULAR 4088682.0   1406813.0
3   A002    R051    02-00-00    04-24-13    00:00:00    REGULAR 4088879 1406839 04-24-13    04:00:00    REGULAR 4088890.0   1406845.0   04-24-13    08:00:00    REGULAR 4088934.0   1406951.0   04-24-13    12:00:00    REGULAR 4089105.0   1407209.0   04-24-13    16:00:00    REGULAR 4089378.0   1407269.0   04-24-13    20:00:00    REGULAR 4090319.0   1407336.0   04-25-13    00:00:00    REGULAR 4090535.0   1407365.0   04-25-13    04:00:00    REGULAR 4090550.0   1407370.0
4   A002    R051    02-00-00    04-25-13    08:00:00    REGULAR 4090589 1407469 04-25-13    08:57:03    DOOR OPEN   4090629.0   1407591.0   04-25-13    08:58:01    LOGON   4090629.0   1407591.0   04-25-13    09:01:08    LGF-MAN 4090629.0   1407591.0   04-25-13    09:01:53    LOGON   4090629.0   1407591.0   04-25-13    09:02:02    DOOR CLOSE  4090629.0   1407591.0   04-25-13    09:02:04    DOOR OPEN   4090629.0   1407591.0   04-25-13    09:02:31    DOOR CLOSE  4090629.0   1407591.0
5   A002    R051    02-00-00    04-25-13    09:02:32    DOOR OPEN   4090629 1407591 04-25-13    09:07:21    LOGON   4090629.0   1407591.0   04-25-13    09:12:12    LGF-MAN 4090642.0   1407592.0   04-25-13    09:12:20    DOOR CLOSE  4090642.0   1407592.0   04-25-13    12:00:00    REGULAR 4090743.0   1407723.0   04-25-13    16:00:00    REGULAR 4091064.0   1407793.0   04-25-13    20:00:00    REGULAR 4092044.0   1407840.0   04-26-13    00:00:00    REGULAR 4092314.0   1407859.0
6   A002    R051    02-00-00    04-26-13    04:00:00    REGULAR 4092325 1407861 04-26-13    08:00:00    REGULAR 4092363.0   1407958.0   04-26-13    12:00:00    REGULAR 4092541.0   1408225.0   04-26-13    16:00:00    REGULAR 4092837.0   1408285.0   04-26-13    20:00:00    REGULAR 4093823.0   1408341.0   None    None    None    NaN NaN None    None    None    NaN NaN None    None    None    NaN NaN
</code>```

My current inefficient function looks like this:

```<code>def cleanData(dataFrame):

    tempDf = dataFrame

    tempColName = ['date','time','description','entries','exit','c/a','unit', 'scp']
    finalColName = ['c/a','unit','scp','date','time','description','entries','exit']

    tempDf1 = tempDf.iloc[:,:8]
    tempDf1.dropna(inplace=True)
    tempDf1.columns = finalColName

    tempDf2 = tempDf.iloc[:,8:13]
    tempDf2['c/a'] = tempDf['C/A']
    tempDf2['unit'] = tempDf['UNIT']
    tempDf2['scp'] = tempDf['SCP']
    tempDf2.dropna(inplace=True)
    tempDf2.columns = tempColName

    tempDf3 = tempDf.iloc[:,13:18]
    tempDf3['c/a'] = tempDf['C/A']
    tempDf3['unit'] = tempDf['UNIT']
    tempDf3['scp'] = tempDf['SCP']
    tempDf3.dropna(inplace=True)
    tempDf3.columns = tempColName

    tempDf4 = tempDf.iloc[:,18:23]
    tempDf4['c/a'] = tempDf['C/A']
    tempDf4['unit'] = tempDf['UNIT']
    tempDf4['scp'] = tempDf['SCP']
    tempDf4.dropna(inplace=True)
    tempDf4.columns = tempColName

    tempDf5 = tempDf.iloc[:,23:28]
    tempDf5['c/a'] = tempDf['C/A']
    tempDf5['unit'] = tempDf['UNIT']
    tempDf5['scp'] = tempDf['SCP']
    tempDf5.dropna(inplace=True)
    tempDf5.columns = tempColName

    tempDf6 = tempDf.iloc[:,28:33]
    tempDf6['c/a'] = tempDf['C/A']
    tempDf6['unit'] = tempDf['UNIT']
    tempDf6['scp'] = tempDf['SCP']
    tempDf6.dropna(inplace=True)
    tempDf6.columns = tempColName

    tempDf7 = tempDf.iloc[:,33:38]
    tempDf7['c/a'] = tempDf['C/A']
    tempDf7['unit'] = tempDf['UNIT']
    tempDf7['scp'] = tempDf['SCP']
    tempDf7.dropna(inplace=True)
    tempDf7.columns = tempColName

    tempDf8 = tempDf.iloc[:,38:43]
    tempDf8['c/a'] = tempDf['C/A']
    tempDf8['unit'] = tempDf['UNIT']
    tempDf8['scp'] = tempDf['SCP']
    tempDf8.dropna(inplace=True)
    tempDf8.columns = tempColName

    placeHolderDf = pd.concat([tempDf2,tempDf3,tempDf4,tempDf5,tempDf6,tempDf7,tempDf8])
    placeHolderDf = placeHolderDf[['c/a','unit','scp','date','time','description','entries','exit']]
    fullData = pd.concat([tempDf1,placeHolderDf])
    fullData['date'] = pd.to_datetime(fullData['date'])

    return fullData.reset_index(drop=True)
</code>```

with a correct final output like:

```<code>    c/a     unit    scp        date         time    description entries exit
0   A002    R051    02-00-00    2013-04-20  00:00:00    REGULAR 4084276 1405308
1   A002    R051    02-00-00    2013-04-21  08:00:00    REGULAR 4085336 1405603
2   A002    R051    02-00-00    2013-04-22  16:00:00    REGULAR 4086116 1406242
3   A002    R051    02-00-00    2013-04-24  00:00:00    REGULAR 4088879 1406839
4   A002    R051    02-00-00    2013-04-25  08:00:00    REGULAR 4090589 1407469
</code>```

Any help is immensely appreaciated.

答:
You can try:</p>

```<code>import io
import pandas as pd

s="""C/A,UNIT,SCP,DATE1,TIME1,DESC1,ENTRIES1,EXITS1,DATE2,TIME2,DESC2,ENTRIES2,EXITS2
   A002,R051,02-00-00,04-20-13,00:00:00,REGULAR,4084276,1405308,04-20-13,04:00:00,REGULAR,4084308.0,1405312.0
   A002,R051,02-00-00,04-25-13,09:02:32,DOOR OPEN,4090629,1407591,04-25-13,09:07:21,LOGON,4090629.0,1407591.0
   A002,R051,02-00-00,04-26-13,04:00:00,REGULAR,4092325,1407861,04-26-13,08:00:00,REGULAR,4092363.0,1407958.0
"""

df = pd.read_csv(io.StringIO(s), sep=',')

col_names = ['C/A', 'UNIT', 'SCP', 'DATE', 'TIME', 'DESC', 'ENTRIES', 'EXITS']

i = 0
nr = 2 # change to 8 with your file
df_dict = dict()
while i < nr:
    i+=1
    df_dict[i] = df.loc[:, [column for column in df.columns[:3]] + [column for column in df.columns if column.endswith(str(i))]]
    new_cols = {x: y for x, y in zip(df_dict[i], df_new.columns)}
    df_dict[i] = df_dict[i].rename(columns=new_cols)

df_new = pd.concat(df_dict.values())
print(df_new)
</code>```

Body:
Right now I've got a process to extract a large amount of data (~1.5m rows) from a URL that comes in an unorganized way that I need to subsequently regorganize. The current process works flawlessly but it is extremely memory heavy and inefficient so I was looking for help.

The data that I receive comes in the following structure: (notice after exit 8 there are another 5 columns of Na's and None signifying end of current SCP

['C/A','UNIT','SCP','DATE1','TIME1','DESC1','ENTRIES1','EXITS1','DATE2','TIME2','ESC2',\
 'ENTRIES2','EXITS2','DATE3','TIME3','DESC3','ENTRIES3','EXITS3','DATE4','TIME4','DESC4',\
 'ENTRIES4','EXITS4','DATE5','TIME5','DESC5','ENTRIES5','EXITS5','DATE6','TIME6','DESC6',\
 'ENTRIES6','EXITS6','DATE7','TIME7','DESC7','ENTRIES7','EXITS7','DATE8','TIME8','DESC8',\
 'ENTRIES8','EXITS8']

My goal is to have it reorganized like such:

['c/a','unit','scp','date','time','description','entries','exit']

Example of Raw Output:

     C/A        UNIT    SCP     DATE1   TIME1   DESC1   ENTRIES1    EXITS1  DATE2   TIME2   ESC2    ENTRIES2    EXITS2  DATE3   TIME3   DESC3   ENTRIES3    EXITS3  DATE4   TIME4   DESC4   ENTRIES4    EXITS4  DATE5   TIME5   DESC5   ENTRIES5    EXITS5  DATE6   TIME6   DESC6   ENTRIES6    EXITS6  DATE7   TIME7   DESC7   ENTRIES7    EXITS7  DATE8   TIME8   DESC8   ENTRIES8    EXITS8
0   A002    R051    02-00-00    04-20-13    00:00:00    REGULAR 4084276 1405308 04-20-13    04:00:00    REGULAR 4084308.0   1405312.0   04-20-13    08:00:00    REGULAR 4084332.0   1405348.0   04-20-13    12:00:00    REGULAR 4084429.0   1405441.0   04-20-13    16:00:00    REGULAR 4084714.0   1405494.0   04-20-13    20:00:00    REGULAR 4085107.0   1405550.0   04-21-13    00:00:00    REGULAR 4085286.0   1405578.0   04-21-13    04:00:00    REGULAR 4085317.0   1405582.0
1   A002    R051    02-00-00    04-21-13    08:00:00    REGULAR 4085336 1405603 04-21-13    12:00:00    REGULAR 4085421.0   1405673.0   04-21-13    16:00:00    REGULAR 4085543.0   1405725.0   04-21-13    20:00:00    REGULAR 4085543.0   1405781.0   04-22-13    00:00:00    REGULAR 4085669.0   1405820.0   04-22-13    04:00:00    REGULAR 4085684.0   1405825.0   04-22-13    08:00:00    REGULAR 4085715.0   1405929.0   04-22-13    12:00:00    REGULAR 4085878.0   1406175.0
2   A002    R051    02-00-00    04-22-13    16:00:00    REGULAR 4086116 1406242 04-22-13    20:00:00    REGULAR 4086986.0   1406310.0   04-23-13    00:00:00    REGULAR 4087164.0   1406335.0   04-23-13    04:00:00    REGULAR 4087172.0   1406339.0   04-23-13    08:00:00    REGULAR 4087214.0   1406441.0   04-23-13    12:00:00    REGULAR 4087390.0   1406685.0   04-23-13    16:00:00    REGULAR 4087738.0   1406741.0   04-23-13    20:00:00    REGULAR 4088682.0   1406813.0
3   A002    R051    02-00-00    04-24-13    00:00:00    REGULAR 4088879 1406839 04-24-13    04:00:00    REGULAR 4088890.0   1406845.0   04-24-13    08:00:00    REGULAR 4088934.0   1406951.0   04-24-13    12:00:00    REGULAR 4089105.0   1407209.0   04-24-13    16:00:00    REGULAR 4089378.0   1407269.0   04-24-13    20:00:00    REGULAR 4090319.0   1407336.0   04-25-13    00:00:00    REGULAR 4090535.0   1407365.0   04-25-13    04:00:00    REGULAR 4090550.0   1407370.0
4   A002    R051    02-00-00    04-25-13    08:00:00    REGULAR 4090589 1407469 04-25-13    08:57:03    DOOR OPEN   4090629.0   1407591.0   04-25-13    08:58:01    LOGON   4090629.0   1407591.0   04-25-13    09:01:08    LGF-MAN 4090629.0   1407591.0   04-25-13    09:01:53    LOGON   4090629.0   1407591.0   04-25-13    09:02:02    DOOR CLOSE  4090629.0   1407591.0   04-25-13    09:02:04    DOOR OPEN   4090629.0   1407591.0   04-25-13    09:02:31    DOOR CLOSE  4090629.0   1407591.0
5   A002    R051    02-00-00    04-25-13    09:02:32    DOOR OPEN   4090629 1407591 04-25-13    09:07:21    LOGON   4090629.0   1407591.0   04-25-13    09:12:12    LGF-MAN 4090642.0   1407592.0   04-25-13    09:12:20    DOOR CLOSE  4090642.0   1407592.0   04-25-13    12:00:00    REGULAR 4090743.0   1407723.0   04-25-13    16:00:00    REGULAR 4091064.0   1407793.0   04-25-13    20:00:00    REGULAR 4092044.0   1407840.0   04-26-13    00:00:00    REGULAR 4092314.0   1407859.0
6   A002    R051    02-00-00    04-26-13    04:00:00    REGULAR 4092325 1407861 04-26-13    08:00:00    REGULAR 4092363.0   1407958.0   04-26-13    12:00:00    REGULAR 4092541.0   1408225.0   04-26-13    16:00:00    REGULAR 4092837.0   1408285.0   04-26-13    20:00:00    REGULAR 4093823.0   1408341.0   None    None    None    NaN NaN None    None    None    NaN NaN None    None    None    NaN NaN

My current inefficient function looks like this:

def cleanData(dataFrame):

    tempDf = dataFrame

    tempColName = ['date','time','description','entries','exit','c/a','unit', 'scp']
    finalColName = ['c/a','unit','scp','date','time','description','entries','exit']

    tempDf1 = tempDf.iloc[:,:8]
    tempDf1.dropna(inplace=True)
    tempDf1.columns = finalColName

    tempDf2 = tempDf.iloc[:,8:13]
    tempDf2['c/a'] = tempDf['C/A']
    tempDf2['unit'] = tempDf['UNIT']
    tempDf2['scp'] = tempDf['SCP']
    tempDf2.dropna(inplace=True)
    tempDf2.columns = tempColName

    tempDf3 = tempDf.iloc[:,13:18]
    tempDf3['c/a'] = tempDf['C/A']
    tempDf3['unit'] = tempDf['UNIT']
    tempDf3['scp'] = tempDf['SCP']
    tempDf3.dropna(inplace=True)
    tempDf3.columns = tempColName

    tempDf4 = tempDf.iloc[:,18:23]
    tempDf4['c/a'] = tempDf['C/A']
    tempDf4['unit'] = tempDf['UNIT']
    tempDf4['scp'] = tempDf['SCP']
    tempDf4.dropna(inplace=True)
    tempDf4.columns = tempColName

    tempDf5 = tempDf.iloc[:,23:28]
    tempDf5['c/a'] = tempDf['C/A']
    tempDf5['unit'] = tempDf['UNIT']
    tempDf5['scp'] = tempDf['SCP']
    tempDf5.dropna(inplace=True)
    tempDf5.columns = tempColName

    tempDf6 = tempDf.iloc[:,28:33]
    tempDf6['c/a'] = tempDf['C/A']
    tempDf6['unit'] = tempDf['UNIT']
    tempDf6['scp'] = tempDf['SCP']
    tempDf6.dropna(inplace=True)
    tempDf6.columns = tempColName

    tempDf7 = tempDf.iloc[:,33:38]
    tempDf7['c/a'] = tempDf['C/A']
    tempDf7['unit'] = tempDf['UNIT']
    tempDf7['scp'] = tempDf['SCP']
    tempDf7.dropna(inplace=True)
    tempDf7.columns = tempColName

    tempDf8 = tempDf.iloc[:,38:43]
    tempDf8['c/a'] = tempDf['C/A']
    tempDf8['unit'] = tempDf['UNIT']
    tempDf8['scp'] = tempDf['SCP']
    tempDf8.dropna(inplace=True)
    tempDf8.columns = tempColName

    placeHolderDf = pd.concat([tempDf2,tempDf3,tempDf4,tempDf5,tempDf6,tempDf7,tempDf8])
    placeHolderDf = placeHolderDf[['c/a','unit','scp','date','time','description','entries','exit']]
    fullData = pd.concat([tempDf1,placeHolderDf])
    fullData['date'] = pd.to_datetime(fullData['date'])

    return fullData.reset_index(drop=True)

with a correct final output like:

    c/a     unit    scp        date         time    description entries exit
0   A002    R051    02-00-00    2013-04-20  00:00:00    REGULAR 4084276 1405308
1   A002    R051    02-00-00    2013-04-21  08:00:00    REGULAR 4085336 1405603
2   A002    R051    02-00-00    2013-04-22  16:00:00    REGULAR 4086116 1406242
3   A002    R051    02-00-00    2013-04-24  00:00:00    REGULAR 4088879 1406839
4   A002    R051    02-00-00    2013-04-25  08:00:00    REGULAR 4090589 1407469

Any help is immensely appreaciated.

Body:
You can try:

import io
import pandas as pd

s="""C/A,UNIT,SCP,DATE1,TIME1,DESC1,ENTRIES1,EXITS1,DATE2,TIME2,DESC2,ENTRIES2,EXITS2
   A002,R051,02-00-00,04-20-13,00:00:00,REGULAR,4084276,1405308,04-20-13,04:00:00,REGULAR,4084308.0,1405312.0
   A002,R051,02-00-00,04-25-13,09:02:32,DOOR OPEN,4090629,1407591,04-25-13,09:07:21,LOGON,4090629.0,1407591.0
   A002,R051,02-00-00,04-26-13,04:00:00,REGULAR,4092325,1407861,04-26-13,08:00:00,REGULAR,4092363.0,1407958.0
"""

df = pd.read_csv(io.StringIO(s), sep=',')

col_names = ['C/A', 'UNIT', 'SCP', 'DATE', 'TIME', 'DESC', 'ENTRIES', 'EXITS']

i = 0
nr = 2 # change to 8 with your file
df_dict = dict()
while i < nr:
    i+=1
    df_dict[i] = df.loc[:, [column for column in df.columns[:3]] + [column for column in df.columns if column.endswith(str(i))]]
    new_cols = {x: y for x, y in zip(df_dict[i], df_new.columns)}
    df_dict[i] = df_dict[i].rename(columns=new_cols)

df_new = pd.concat(df_dict.values())
print(df_new)


来源: stackexchange

问:
Passing an *os.File as an *io.Reader
If there's a function that takes a *io.Reader, how would one pass an *io.File?  This doesn't work:

```<code>func somefunc(r *io.Reader){
}

func Test_FileReader(t *testing.T){
    f, err := os.Open("xyz")
    assert.NoError(t, err)

    somefunc(&f)
}
</code>```

It gives this error:

Cannot use '&f' (type **File) as type *io.Reader

Then, changing *io.Reader->io.Reader and &f->f, it compiles just fine:

```<code>func somefunc(r io.Reader){
}

func Test_FileReader(t *testing.T){
    f, err := os.Open("xyz")
    assert.NoError(t, err)

    somefunc(f)
}
</code>```

Is it even possible to pass an *os.File as an *io.Reader??

答:
Is it even possible to pass an *os.File as an *io.Reader?

No, of course not.

You seem to know that *os.File implement io.Reader and that **os.File does not. Note that you never *) need a pointer to interface.

*) There are cases when you need a pointer to interface and you will know when, its a very special and really uncommon case.

ContentLicense: CC BY-SA 4.0

Title: Passing an *os.File as an *io.Reader

Body:
If there's a function that takes a *io.Reader, how would one pass an *io.File?  This doesn't work:

func somefunc(r *io.Reader){
}

func Test_FileReader(t *testing.T){
    f, err := os.Open("xyz")
    assert.NoError(t, err)

    somefunc(&f)
}

It gives this error:

Cannot use '&f' (type **File) as type *io.Reader

Then, changing *io.Reader->io.Reader and &f->f, it compiles just fine:

func somefunc(r io.Reader){
}

func Test_FileReader(t *testing.T){
    f, err := os.Open("xyz")
    assert.NoError(t, err)

    somefunc(f)
}

Is it even possible to pass an *os.File as an *io.Reader??


问:
Scan Java classpath for lone subclass?
I have a rather unique situation where I know my Java web app will always be packaged with 1-and-only-1 concrete subclass of an AbstractWidget:

```<code>public abstract class AbstractWidget {
    // ...
}

public class SimpleWidget extends AbstractWidget {
    // ...
}

public class ComplexWidget extends AbstractWidget {
    // ...
}

public class CrazyComplexWidget extends AbstractWidget {
    // ...
}

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

Again, I know at runtime that my WAR/WEB-INF/classes directory will always have 1-and-only-1 AbstractWidget impl packaged in it (no more, no less), be it ComplexWidget.class, SimpleWidget.class, etc.

I'm trying to construct code (that would actually run when the WAR starts up from inside its ServletContextListener impl) that would be able to scan the runtime classpath and obtain an instance (using public no-arg constructor) of the AbstractWidget.

Thus, if my WAR has:

```<code>myWar/
    WEB-INF/
        lib/
        classes/
            com/
                myorg/
                    App (implements ServletContextListener)
                    ... lots of other classes and packages
                    some/
                        arbitrary/
                            package/
                                SimpleWidget
</code>```

Then, from inside App#contextInitialized(ServletContextEvent) I need code that will find SimpleWidget.class on the classpath and give me an instance of it:

```<code>public class App implements ServletContextListener {
    @Override
    public void contextInitialized(ServletContextEvent event) {
        // Scan classpath for the lone AbstractWidget impl somehow.
        ???

        // Use public, no-arg ctor to instantiate the impl.
        AbstractWidget widget = ???

        // Now do stuff with widget...
    }
}
</code>```

I know you can use reflection methods like Class.isAssignableFrom(), but not sure if that is the correct way to go, and even if it is, how to use it for my given use case. Any ideas? Thanks in advance!

答:
I know it is very old question but ClassGraph library can help you very easily get all classes in the classpath extending a given class, basically all subclasses of a given class.

Gradle dependency

```<code>compile group: 'io.github.classgraph', name: 'classgraph', version: '4.8.46'
</code>```

This how you can easily you can scan classpath

```<code>ScanResult scanResult = new ClassGraph()
    .whitelistPackages("com.myorg") //whatever package you want to scan
    .verbose()
    .enableAllInfo()
    .scan();

System.out.println("Classes which extending '" + AbstractWidget.class.getSimpleName() + "' class");
ClassInfoList classInfoList = scanResult.getSubclasses(AbstractWidget.class.getName());
for (ClassInfo classInfo : classInfoList) {
    System.out.println("\t" + classInfo.getName());
}
</code>```

Output assuming you have SimpleWidget class in the classpath

```<code>Classes which extending 'AbstractWidget' class
    com.myorg.some.arbitrary.package.SimpleWidget
</code>```

I hope it helps. ClassGraph is really powerful library and can do much more this simple need. Its explain really well in this article https://readtorakesh.com/java-classpath-scanning-using-classgraph/

ContentLicense: CC BY-SA 3.0

Title: Scan Java classpath for lone subclass?

Body:
I have a rather unique situation where I know my Java web app will always be packaged with 1-and-only-1 concrete subclass of an AbstractWidget:

public abstract class AbstractWidget {
    // ...
}

public class SimpleWidget extends AbstractWidget {
    // ...
}

public class ComplexWidget extends AbstractWidget {
    // ...
}

public class CrazyComplexWidget extends AbstractWidget {
    // ...
}

// ...etc.

Again, I know at runtime that my WAR/WEB-INF/classes directory will always have 1-and-only-1 AbstractWidget impl packaged in it (no more, no less), be it ComplexWidget.class, SimpleWidget.class, etc.

I'm trying to construct code (that would actually run when the WAR starts up from inside its ServletContextListener impl) that would be able to scan the runtime classpath and obtain an instance (using public no-arg constructor) of the AbstractWidget.

Thus, if my WAR has:

myWar/
    WEB-INF/
        lib/
        classes/
            com/
                myorg/
                    App (implements ServletContextListener)
                    ... lots of other classes and packages
                    some/
                        arbitrary/
                            package/
                                SimpleWidget

Then, from inside App#contextInitialized(ServletContextEvent) I need code that will find SimpleWidget.class on the classpath and give me an instance of it:

public class App implements ServletContextListener {
    @Override
    public void contextInitialized(ServletContextEvent event) {
        // Scan classpath for the lone AbstractWidget impl somehow.
        ???

        // Use public, no-arg ctor to instantiate the impl.
        AbstractWidget widget = ???

        // Now do stuff with widget...
    }
}

I know you can use reflection methods like Class.isAssignableFrom(), but not sure if that is the correct way to go, and even if it is, how to use it for my given use case. Any ideas? Thanks in advance!

Body:
I know it is very old question but ClassGraph library can help you very easily get all classes in the classpath extending a given class, basically all subclasses of a given class.

Gradle dependency

compile group: 'io.github.classgraph', name: 'classgraph', version: '4.8.46'

This how you can easily you can scan classpath

ScanResult scanResult = new ClassGraph()
    .whitelistPackages("com.myorg") //whatever package you want to scan
    .verbose()
    .enableAllInfo()
    .scan();

System.out.println("Classes which extending '" + AbstractWidget.class.getSimpleName() + "' class");
ClassInfoList classInfoList = scanResult.getSubclasses(AbstractWidget.class.getName());
for (ClassInfo classInfo : classInfoList) {
    System.out.println("\t" + classInfo.getName());
}

Output assuming you have SimpleWidget class in the classpath

Classes which extending 'AbstractWidget' class
    com.myorg.some.arbitrary.package.SimpleWidget

I hope it helps. ClassGraph is really powerful library and can do much more this simple need. Its explain really well in this article https://readtorakesh.com/java-classpath-scanning-using-classgraph/


问:
Defending "U" suffix after Hex literals
There is some debate between my colleague and I about the U suffix after hexadecimally represented literals. Note, this is not a question about the meaning of this suffix or about what it does. I have found several of those topics here, but I have not found an answer to my question.

Some background information:

We're trying to come to a set of rules that we both agree on, to use that as our style from that point on. We have a copy of the 2004 Misra C rules and decided to use that as a starting point. We're not interested in being fully Misra C compliant; we're cherry picking the rules that we think will most increase efficiency and robustness.

Rule 10.6 from the aforementioned guidelines states:

A “U” suffix shall be applied to all constants of unsigned type.

I personally think this is a good rule. It takes little effort, looks better than explicit casts and more explicitly shows the intention of a constant. To me it makes sense to use it for all unsigned contants, not just numerics, since enforcing a rule doesn't happen by allowing exceptions, especially for a commonly used representation of constants.

My colleague, however, feels that the hexadecimal representation doesn't need the suffix. Mostly because we almost exclusively use it to set micro-controller registers, and signedness doesn't matter when setting registers to hex constants.

My Question

My question is not one about who is right or wrong. It is about finding out whether there are cases where the absence or presence of the suffix changes the outcome of an operation. Are there any such cases, or is it a matter of consistency?

Edit: for clarification; Specifically about setting micro-controller registers by assigning hexadecimal values to them. Would there be a case where the suffix could make a difference there? I feel like it wouldn't. As an example, the Freescale Processor Expert generates all register assignments as unsigned.

答: For the specific question, which was loading register(s), then the U makes it an unsigned value, but whether the compiler treats the n-bit word pattern as a signed or unsigned value it will move the same bit pattern, assuming there isn't any size extension that would propagate an MSB. The difference that might matter is if the register load operation will set any processor condition flags based on a signed or unsigned loading. As an overall guide if the processor supports storing a constant to configuration register or a memory address then loading a peripheral register is unlikely to set the processor's NEG condition flag. Loading a general purpose register connected to an ALU, one that can be the target of an arithmetic operation like add increment or decrement, might set a negative flag on loading so that e.g. a trailing "branch (if) negative" opcode would execute the branch. You would want to check the processor's references to be sure. Small instruction set processors tend to have only a load register instruction, while larger instruction sets are more likely to have a load unsigned variant of the load instruction that doesn't set the NEG bit in the processor's flags, but again, check the processor's references. if you don't have access to the processor's errata (the boo-boo list) and need a specific flag state. All of this only tends to come up when an optimizing compiler re-aranges code with an inline assembly instruction and other uncommon situations. Examine the generate assembly code, turn off some or all compiler optimizations for the module when needed, etc.

Title: Defending "U" suffix after Hex literals


问:
ui-scroll for long text virtualising
What is the best way of showing a very long text (in MBs) using ui-scroll ? The text is available in the form of an array but needs to be displayed as a long text document just like a textarea. I'm using ui-scroll as each word in the text is a clickable anchor tag.

Is ui-scroll-td to display each word as a column and lines as row the only way ?

More Information

I have a large array containing > 2000 elements (words) which can be
changed by user dynamically (both the number of elements and the
element itself).

I need to display these elements as a single document where each element (word) is a hyperlink which when clicked performs certain action.

I need it to look like a simple scrollable div which has these words displayed as long free flowing clickable text.

I was using $compile earlier to create html dynamically but the initial compile time and the compile upon element change is very significant (seconds) as I have to compile/render the whole list of elements every single time.

So I thought of using ui-scroll to display which virtualizes and renders only the displayed content.

But ui-scroll always display each element in a separate line like a row which is not the desired behavior for my use case.

```<code>Input:
    myList = ["Hello", "World", "This", "is", "my", "first", "dream"].

 desired Output (each word in same line with auto wrap just like a div):

 <div>
     <span ng-repeat="w in myList">
         <a ng-click="someAction()">myList[$index]</a>

     </span>
  </div>

  Hello World This is my first dream

</code>```

```<code>ui-scroll:
</code>```

```<code>    <div>
       <span ui-scroll="at in info">
          <a id="at-{{$index}}" ng-click="someAction($index, $event)">
             myList[$index]}}
          </a>

       </span>
      </div>

  ui-scroll output (each word in separate line):
  Hello
   World
   This
   is
   my
   first
   dream
</code>```

Kindly note that each word above is a clickable anchor.
Thanks.

答:
Unfortunately, this is completely impossible with the angular-ui-scroll as it does not support inline/floating elements. There were some attempts to implement this feature in 2015, but now it seems frozen forever.

Infinite approach

The case you are developing is very interesting. As a possible workaround I would advise to try "infinite" approach instead of "virtualizing" one. Infinite scroll could be implemented with no additional libraries, the idea could be broken into following steps:

add N words into the viewport initially

if viewport's scrollHeight === clientHeight, add N more words; make a loop until the scrollbar appears

listen for viewport's scrollTop changing, invoke following when scroll happens

if scrollTop + clientHeight === scrollHeight add N more words; also in a loop until the height of the viewport (scrollHeight) increases or until words are over

This should drastically reduce the cost of the initial render, but since the elements out of the view are not being destroyed, the overall performance will decrease per each new elements injection.

Virtualization

After the "infinite" approach is implemented, I think you may try to turn this particular infinite scroll case into virtual one. Let's think what might be required. First, you'll need two additional elements, say, top and bottom padding elements, which will emulate virtual words. Then you will need to extend the last step of the "infinite" approach by following:

look into the opposite direction and find the first element that is visible in the viewport; this could be done in multiple ways (here and hundreds of other links)

remember scrollHeight, clip off all the elements before the found one, set the height of the top padding element into remembered value to make the the result scrollHeight the same as before clipping

depends on the environment/requirements you also might need to correct scroll position as it might jump up during clipping; I would like not to discuss back-jumping here, just be happy with the default overflow-anchor behaviour (though you'll have to forget about Edge and other sad guys)

the condition scrollTop + clientHeight === scrollHeight in our handler (say, "if we are at the very bottom") should be reconsidered as we may have non-zero bottom padding element; so it should be like "if we are at the very bottom OR if the bottom padding element becomes visible"

if the condition above is fulfilled, you need to add N words again and again until the bottom padding becomes invisible again, and each injection should be accomplished with decreasing of the bottom padding element's height by the value the viewport's scrollHeight is increased with; and only the edge case when "we are at the very bottom" will cause the irrevocable increasing of the viewport's scrollHeight

This way we will properly cover downward scrolling. You obviously will need to take into account upward scrolling and run the similar procedure when "the top padding becomes visible OR we are at the very top". Also the "we are at very top/bottom" and "element becomes visible" conditions could be extended with some UX-friendly deltas, say, "we are almost at..." and "the element is almost visible".

I don't think this plan covers or should cover all possible edge cases and requirements, it's just an idea how this could be done from scratch, and my guess is it's the only way you have, I mean the complete implementation by yourself. I would be happy if I'm wrong and would love to look at someone's ready-made solution.

Title: ui-scroll for long text virtualising

Body:
What is the best way of showing a very long text (in MBs) using ui-scroll ? The text is available in the form of an array but needs to be displayed as a long text document just like a textarea. I'm using ui-scroll as each word in the text is a clickable anchor tag.

Is ui-scroll-td to display each word as a column and lines as row the only way ?

More Information

I have a large array containing > 2000 elements (words) which can be
changed by user dynamically (both the number of elements and the
element itself).

I need to display these elements as a single document where each element (word) is a hyperlink which when clicked performs certain action.

I need it to look like a simple scrollable div which has these words displayed as long free flowing clickable text.

I was using $compile earlier to create html dynamically but the initial compile time and the compile upon element change is very significant (seconds) as I have to compile/render the whole list of elements every single time.

So I thought of using ui-scroll to display which virtualizes and renders only the displayed content.

But ui-scroll always display each element in a separate line like a row which is not the desired behavior for my use case.

Input:
    myList = ["Hello", "World", "This", "is", "my", "first", "dream"].

 desired Output (each word in same line with auto wrap just like a div):

         myList[$index]

  Hello World This is my first dream

ui-scroll:

             myList[$index]}}

  ui-scroll output (each word in separate line):
  Hello
   World
   This
   is
   my
   first
   dream

Kindly note that each word above is a clickable anchor.
Thanks.


问:
I am unable to get a Picker SelectedItem value on another page
I am trying to get a picker SelectedItem value for use on another page, but it doesn't seem to want to work.
I found a "solution" here:Xamarin Picker selected item persisting on multiple pages but it doesn't want to work for me. When I try to use it, in say a label, I can't seem to get it to display. I can manually set the string, but not use the string of the actual selected item.
I created the static class as directed:

```<code>public static class PickerPersist
{
    public static string pickerSelectedItem = "test";
}
</code>```

and included the xmlns:local="clr-namespace:C971" and the label binding to Text="{Binding x:static local:PickerPersist.PickerSelectedItem}"and it correctly shows "test" when displaying the label.
My question is, how do I get the string of the actual SelectedItem to show? The Picker itself is on another page, and I was able to get it to display correctly with var pickerItem = (Terms)termpicker.SelectedItem;
I tried setting the PickerSelectedItem to Page1.termPicker.SelectedItem; but it comes back with the error "Page1.termPicker is inaccessible due to its protection level".
I was able to get rid of the error by setting public static string Picked; on the previous page class, and then setting the variable pickerItem to Picked. However, it doesn't seem to carry across to the new page. If I put a break after setting pickeritem to Picked everything shows that it is indeed set correctly, but it shows as null in the PickerPersist and thus not showing on the new page.

I hope all this makes sense.
Any help you can offer would be great!! Thanks!
Full code available here: https://github.com/yax51/C971.git

Xaml where Picker lives:

```<code>ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             xmlns:local="clr-namespace:C971"
             x:Class="C971.Page1"
             Title="Term Selector">

    <ContentPage.Content>
        <StackLayout>

            <Picker x:Name="termPicker"
                    ItemsSource="{Binding Terms}"
                    ItemDisplayBinding="{Binding TermName}"
                    Title="Select a term"
                    IsEnabled="True"
                    SelectedItem="{x:Static local:PickerPersist.PickerSelectedItem}"/>
</code>```

Xaml.cs

```<code>
    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class Page1 : ContentPage
    {
        public static String Picked;
        public Page1()
        {
            InitializeComponent();

        }
    ...

        void TermGoClicked(object sender, EventArgs e)
        {
            var pickerItem = (Terms)termPicker.SelectedItem;

            if (pickerItem == null)
            {
                DisplayAlert("Error", "Please Select a term", "Ok");
            }
            else
            {
                Picked = pickerItem.TermName;

                Navigation.PushAsync(new TermPage());
            }

        }

</code>```

Static class

```<code>public static class PickerPersist
    {
        public static string PickerSelectedItem = Page1.Picked;
    }
</code>```

Label on new page

```<code>    <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             xmlns:local="clr-namespace:C971"
             x:Class="C971.TermPage">
    <ContentPage.Content>
        <StackLayout>
            <Label x:Name="test" Text="{x:Static local:PickerPersist.PickerSelectedItem}"/>
        </StackLayout>
    </ContentPage.Content>
</ContentPage>

</code>```

答:
Use Picker listener SelectedIndexChange, Create a view model update the property of view model when the selected item changed, and bind the text with label with view model property. – @Muhammad Adeel Shoukat

Did this and needed to add a ViewCell into the ListView and it worked.

Body:
I am trying to get a picker SelectedItem value for use on another page, but it doesn't seem to want to work.
I found a "solution" here:Xamarin Picker selected item persisting on multiple pages but it doesn't want to work for me. When I try to use it, in say a label, I can't seem to get it to display. I can manually set the string, but not use the string of the actual selected item.
I created the static class as directed:

public static class PickerPersist
{
    public static string pickerSelectedItem = "test";
}

and included the xmlns:local="clr-namespace:C971" and the label binding to Text="{Binding x:static local:PickerPersist.PickerSelectedItem}"and it correctly shows "test" when displaying the label.
My question is, how do I get the string of the actual SelectedItem to show? The Picker itself is on another page, and I was able to get it to display correctly with var pickerItem = (Terms)termpicker.SelectedItem;
I tried setting the PickerSelectedItem to Page1.termPicker.SelectedItem; but it comes back with the error "Page1.termPicker is inaccessible due to its protection level".
I was able to get rid of the error by setting public static string Picked; on the previous page class, and then setting the variable pickerItem to Picked. However, it doesn't seem to carry across to the new page. If I put a break after setting pickeritem to Picked everything shows that it is indeed set correctly, but it shows as null in the PickerPersist and thus not showing on the new page.

I hope all this makes sense.
Any help you can offer would be great!! Thanks!
Full code available here: https://github.com/yax51/C971.git

Xaml where Picker lives:

ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             xmlns:local="clr-namespace:C971"
             x:Class="C971.Page1"
             Title="Term Selector">

    <ContentPage.Content>
        <StackLayout>

            <Picker x:Name="termPicker"
                    ItemsSource="{Binding Terms}"
                    ItemDisplayBinding="{Binding TermName}"
                    Title="Select a term"
                    IsEnabled="True"
                    SelectedItem="{x:Static local:PickerPersist.PickerSelectedItem}"/>

Xaml.cs

    [XamlCompilation(XamlCompilationOptions.Compile)]
    public partial class Page1 : ContentPage
    {
        public static String Picked;
        public Page1()
        {
            InitializeComponent();

        }
    ...

        void TermGoClicked(object sender, EventArgs e)
        {
            var pickerItem = (Terms)termPicker.SelectedItem;

            if (pickerItem == null)
            {
                DisplayAlert("Error", "Please Select a term", "Ok");
            }
            else
            {
                Picked = pickerItem.TermName;

                Navigation.PushAsync(new TermPage());
            }

        }

Static class

public static class PickerPersist
    {
        public static string PickerSelectedItem = Page1.Picked;
    }

Label on new page

    <ContentPage xmlns="http://xamarin.com/schemas/2014/forms"
             xmlns:x="http://schemas.microsoft.com/winfx/2009/xaml"
             xmlns:d="http://xamarin.com/schemas/2014/forms/design"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
             mc:Ignorable="d"
             xmlns:local="clr-namespace:C971"
             x:Class="C971.TermPage">
    <ContentPage.Content>
        <StackLayout>

        </StackLayout>
    </ContentPage.Content>
</ContentPage>


问:
practicing WHERE and HAVING in SQL
here is the schema:

alt text http://img30.imageshack.us/img30/3324/123vk.jpg

here is the question:

Point out the battles in which at least three ships from the same country took part.

here is my answer:

```<code>  SELECT battles.name
    FROM battles,
         outcomes,
         ships,
         classes
   WHERE outcomes.ship = ships.name
GROUP BY battles.name
  HAVING COUNT(classes.country) >= 3;
</code>```

Can you please tell me why it is wrong and help me to correct it!

答:
SELECT battle
  FROM outcomes, ships, classes
 WHERE outcomes.ship = ships.name AND ships.class = classes.class
 GROUP BY battle
HAVING COUNT(outcomes.ship) >= 3;
```

This will work for your question.

ContentLicense: CC BY-SA 2.5

Title: practicing WHERE and HAVING in SQL

Body:
here is the schema:

alt text http://img30.imageshack.us/img30/3324/123vk.jpg

here is the question:

Point out the battles in which at least three ships from the same country took part.

here is my answer:

  SELECT battles.name
    FROM battles,
         outcomes,
         ships,
         classes
   WHERE outcomes.ship = ships.name
GROUP BY battles.name
  HAVING COUNT(classes.country) >= 3;

Can you please tell me why it is wrong and help me to correct it!

Body:
SELECT battle
  FROM outcomes, ships, classes
 WHERE outcomes.ship = ships.name AND ships.class = classes.class
 GROUP BY battle
HAVING COUNT(outcomes.ship) >= 3;

This will work for your question.


问:
How to optimize the following SELECT query
We have the following table

```<code>id  # primary key

device_id_fk

auth # there's an index on it

old_auth  # there's an index on it
</code>```

And the following query.

```<code>$select_user = $this->db->prepare("
        SELECT device_id_fk
        FROM wtb_device_auths AS dv
        WHERE (dv.auth= :auth OR dv.old_auth= :auth)
        LIMIT 1
");
</code>```

explain, I can't reach the server of the main client, but here's another client with fewer data

Since there's a lot of other updates queries on auth, update queries start getting written to the slow query log and the cpu spikes

If you remove the index from auth, then the select query gets written to the slow query log, but not the update, if you add an index to device_id_fk, it makes no difference.

I tried rewriting the query using union instead of or, but I was told that there was still cpu spike and the select query gets written to the slow query log still

```<code>$select_user = $this->db->prepare("
    (SELECT device_id_fk
     FROM wtb_device_auths
     AS dv WHERE dv.auth= :auth)
    UNION ALL
    (SELECT device_id_fk
     FROM wtb_device_auths AS dv
     WHERE dv.old_auth= :auth)
     LIMIT 1"
    );
");
</code>```

Explain

Most often, this is the only query in the slow query log. Is there a more optimal way to write the query? Is there a more optimal way to add indexes? The client is using an old MariaDB version, the equivalent of MYSQL 5.5, on a centos 6 server running LAMP

Additional info

The update query that gets logged to the slow query log whenever an index is added to auth is

```<code>$update_device_auth = $this->db->prepare("UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk");
</code>```

答:
Your few indexes should not be slowing down your updates.

You need two indexes to make both your update and select perform well. My best guess is you never had both at the same time.

```<code>UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk
</code>```

You need an index on device_id_fk for this update to perform well. And regardless of its index it should be declared a foreign key.

```<code>    SELECT device_id_fk
    FROM wtb_device_auths AS dv
    WHERE (dv.auth= :auth OR dv.old_auth= :auth)
    LIMIT 1
</code>```

You need a single combined index on auth, old_auth for this query to perform well.

Separate auth and old_auth indexes should also work well assuming there's no too many duplicates. MySQL will merge the results from the indexes and that merge should be fast... unless a lot of rows match.

If you also search for old_auth alone, add an index on old_auth.

And, as others have pointed out, the select query could return one of several matching devices with a matching auth or old_auth. This is probably bad. If auth and old_auth are intended to identify a device, add a unique constraint.

Alternatively, you need to restructure your data. Multiple columns holding the same value is a red flag. It can result in a proliferation of indexes, as you're experiencing, and also limit how many versions you can store. Instead, have just one auth per row and allow each device to have multiple rows.

```<code>create table wtb_device_auths (
  id serial primary key,
  device_id bigint not null references wtb_devices(id),
  auth text not null,
  created_at datetime not null default current_timestamp,

  index(auth)
);
</code>```

Now you only need to search one column.

```<code>select device_id from wtb_device_auths where auth = ?
</code>```

Now one device can have many wtb_device_auths rows. If you want the current auth for a device, search for the newest one.

```<code>select device_id
from wtb_device_auths
where device_id = ?
order by created_at desc
limit 1
</code>```

Since each device will only have a few auths, this is likely to be plenty fast with the device_id index alone; sorting the handful of rows for a device will be fast.

If not, you might need an additional combined index like created_at, device_id. This covers searching and sorting by created_at alone as well as queries searching and sorting by both created_at and device_id.

Body:
We have the following table

id  # primary key

device_id_fk

auth # there's an index on it

old_auth  # there's an index on it

And the following query.

$select_user = $this->db->prepare("
        SELECT device_id_fk
        FROM wtb_device_auths AS dv
        WHERE (dv.auth= :auth OR dv.old_auth= :auth)
        LIMIT 1
");

explain, I can't reach the server of the main client, but here's another client with fewer data

Since there's a lot of other updates queries on auth, update queries start getting written to the slow query log and the cpu spikes

If you remove the index from auth, then the select query gets written to the slow query log, but not the update, if you add an index to device_id_fk, it makes no difference.

I tried rewriting the query using union instead of or, but I was told that there was still cpu spike and the select query gets written to the slow query log still

$select_user = $this->db->prepare("
    (SELECT device_id_fk
     FROM wtb_device_auths
     AS dv WHERE dv.auth= :auth)
    UNION ALL
    (SELECT device_id_fk
     FROM wtb_device_auths AS dv
     WHERE dv.old_auth= :auth)
     LIMIT 1"
    );
");

Explain

Most often, this is the only query in the slow query log. Is there a more optimal way to write the query? Is there a more optimal way to add indexes? The client is using an old MariaDB version, the equivalent of MYSQL 5.5, on a centos 6 server running LAMP

Additional info

The update query that gets logged to the slow query log whenever an index is added to auth is

$update_device_auth = $this->db->prepare("UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk");

Body:
Your few indexes should not be slowing down your updates.

You need two indexes to make both your update and select perform well. My best guess is you never had both at the same time.

UPDATE wtb_device_auths SET auth= :auth WHERE device_id_fk= :device_id_fk

You need an index on device_id_fk for this update to perform well. And regardless of its index it should be declared a foreign key.

    SELECT device_id_fk
    FROM wtb_device_auths AS dv
    WHERE (dv.auth= :auth OR dv.old_auth= :auth)
    LIMIT 1

You need a single combined index on auth, old_auth for this query to perform well.

Separate auth and old_auth indexes should also work well assuming there's no too many duplicates. MySQL will merge the results from the indexes and that merge should be fast... unless a lot of rows match.

If you also search for old_auth alone, add an index on old_auth.

And, as others have pointed out, the select query could return one of several matching devices with a matching auth or old_auth. This is probably bad. If auth and old_auth are intended to identify a device, add a unique constraint.

Alternatively, you need to restructure your data. Multiple columns holding the same value is a red flag. It can result in a proliferation of indexes, as you're experiencing, and also limit how many versions you can store. Instead, have just one auth per row and allow each device to have multiple rows.

create table wtb_device_auths (
  id serial primary key,
  device_id bigint not null references wtb_devices(id),
  auth text not null,
  created_at datetime not null default current_timestamp,

  index(auth)
);

Now you only need to search one column.

select device_id from wtb_device_auths where auth = ?

Now one device can have many wtb_device_auths rows. If you want the current auth for a device, search for the newest one.

select device_id
from wtb_device_auths
where device_id = ?
order by created_at desc
limit 1

Since each device will only have a few auths, this is likely to be plenty fast with the device_id index alone; sorting the handful of rows for a device will be fast.

If not, you might need an additional combined index like created_at, device_id. This covers searching and sorting by created_at alone as well as queries searching and sorting by both created_at and device_id.


问:
Docker Hub - Automated Build failed, but local build on my raspberry runs without problems
I have an automated docker build at docker hub that fails while it runs without errors locally (on my raspberry4)

The logs are here, text here:

```<code>Cloning into '.'...
Warning: Permanently added the RSA host key for IP address '192.30.253.112' to the list of known hosts.
Reset branch 'master'
Your branch is up-to-date with 'origin/master'.
KernelVersion: 4.4.0-1060-aws
Components: [{u'Version': u'18.03.1-ee-3', u'Name': u'Engine', u'Details': {u'KernelVersion': u'4.4.0-1060-aws', u'Os': u'linux', u'BuildTime': u'2018-08-30T18:42:30.000000000+00:00', u'ApiVersion': u'1.37', u'MinAPIVersion': u'1.12', u'GitCommit': u'b9a5c95', u'Arch': u'amd64', u'Experimental': u'false', u'GoVersion': u'go1.10.2'}}]
Arch: amd64
BuildTime: 2018-08-30T18:42:30.000000000+00:00
ApiVersion: 1.37
Platform: {u'Name': u''}
Version: 18.03.1-ee-3
MinAPIVersion: 1.12
GitCommit: b9a5c95
Os: linux
GoVersion: go1.10.2
Starting build of index.docker.io/jiwidi/jupyter-lab-rpi:latest...
Step 1/22 : FROM resin/raspberrypi3-python:3.6
---> 97f0dfa04606
Step 2/22 : MAINTAINER
---> Running in e83c3b081120
Removing intermediate container e83c3b081120
---> 231f400a33d1
Step 3/22 : WORKDIR /root
Removing intermediate container 94b752c9953c
---> adc8cb2817d2
Step 4/22 : RUN apt-get update && apt-get install -y libncurses5-dev libzmq-dev libfreetype6-dev libpng-dev
---> Running in ded49f1ec6dd
[91mstandard_init_linux.go:190: exec user process caused "exec format error"
[0m
Removing intermediate container ded49f1ec6dd
The command '/bin/sh -c apt-get update && apt-get install -y libncurses5-dev libzmq-dev libfreetype6-dev libpng-dev' returned a non-zero code: 1
</code>```

The line where it fails doesnt provide much feedback:

The command '/bin/sh -c apt-get update' returned a non-zero code: 1`

I have it linked with my GitHub repository where you can find the Dockerfile

Could this be because my image is building in ARM architecture and docker-hub runs x86?

答:
Hi logs link not working, have you tried separating commands?

something like this to know what is failing?

```<code>RUN apt-get update && apt-get install -y libncurses5-dev
RUN apt-get update && apt-get install -y libzmq-dev
RUN apt-get update && apt-get install -y libfreetype6-dev
RUN apt-get update && apt-get install -y libpng-dev
</code>```

Body:
I have an automated docker build at docker hub that fails while it runs without errors locally (on my raspberry4)

The logs are here, text here:

Cloning into '.'...
Warning: Permanently added the RSA host key for IP address '192.30.253.112' to the list of known hosts.
Reset branch 'master'
Your branch is up-to-date with 'origin/master'.
KernelVersion: 4.4.0-1060-aws
Components: [{u'Version': u'18.03.1-ee-3', u'Name': u'Engine', u'Details': {u'KernelVersion': u'4.4.0-1060-aws', u'Os': u'linux', u'BuildTime': u'2018-08-30T18:42:30.000000000+00:00', u'ApiVersion': u'1.37', u'MinAPIVersion': u'1.12', u'GitCommit': u'b9a5c95', u'Arch': u'amd64', u'Experimental': u'false', u'GoVersion': u'go1.10.2'}}]
Arch: amd64
BuildTime: 2018-08-30T18:42:30.000000000+00:00
ApiVersion: 1.37
Platform: {u'Name': u''}
Version: 18.03.1-ee-3
MinAPIVersion: 1.12
GitCommit: b9a5c95
Os: linux
GoVersion: go1.10.2
Starting build of index.docker.io/jiwidi/jupyter-lab-rpi:latest...
Step 1/22 : FROM resin/raspberrypi3-python:3.6
---> 97f0dfa04606
Step 2/22 : MAINTAINER
---> Running in e83c3b081120
Removing intermediate container e83c3b081120
---> 231f400a33d1
Step 3/22 : WORKDIR /root
Removing intermediate container 94b752c9953c
---> adc8cb2817d2
Step 4/22 : RUN apt-get update && apt-get install -y libncurses5-dev libzmq-dev libfreetype6-dev libpng-dev
---> Running in ded49f1ec6dd
[91mstandard_init_linux.go:190: exec user process caused "exec format error"
[0m
Removing intermediate container ded49f1ec6dd
The command '/bin/sh -c apt-get update && apt-get install -y libncurses5-dev libzmq-dev libfreetype6-dev libpng-dev' returned a non-zero code: 1

The line where it fails doesnt provide much feedback:

The command '/bin/sh -c apt-get update' returned a non-zero code: 1`

I have it linked with my GitHub repository where you can find the Dockerfile

Could this be because my image is building in ARM architecture and docker-hub runs x86?

Body:
Hi logs link not working, have you tried separating commands?

something like this to know what is failing?

RUN apt-get update && apt-get install -y libncurses5-dev
RUN apt-get update && apt-get install -y libzmq-dev
RUN apt-get update && apt-get install -y libfreetype6-dev
RUN apt-get update && apt-get install -y libpng-dev


问:
How System Call Works
I was studying system calls from my operating systems course until this image appeared and I couldn't exactly understand its content.
Can anyone explain to me what is going on in user space and kernel space?

答:
I try to Explain the mechanism of a system call to answer to your question:

When a System call is invoked a software interrupt is raised in user space ( or better in user mode ).

At this point, ever in user mode, the state of the user program is saved into memory.

After that there is the transaction in the kernel mode ( or kernel space ) to handle the interrupt, and return the value to the user space.

Then ,in user mode, the state of the running user program is restored from memory and the execution goes ahead.

I leave you a complete description of system call in this link : Kernel System Call

Title: How System Call Works


问:
Check if scrolled past div with JavaScript (no jQuery)
I am currently learning JavaScript and all the solutions that I've come across use the jQuery library.  Is there a way to do it, just using pure JavaScript?

The idea is to have something like:

```<code>function passed(element) {if passed: do something}
</code>```

答:
```<code>if (element.getBoundingClientRect().y < 0) {
  // do something
}
</code>```

Body:
I am currently learning JavaScript and all the solutions that I've come across use the jQuery library.  Is there a way to do it, just using pure JavaScript?

The idea is to have something like:

function passed(element) {if passed: do something}


问:
Attempt to invoke virtual method 'void androidx.recyclerview.widget.RecyclerView.setHasFixedSize(boolean)
Hi everyone I'm trying to do a realtime search from a firebase database and display the data but I get the error below every time and I can't understand how to fix it. Can anyone help me to fix it? I'm new to Android and I can't understand what's going on.  If anyone could suggest to me what I should do it would be incredible.
Thank you in advance.

Firebase Search Activity

```<code>package com.example.ipill;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.Context;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup;
import android.widget.EditText;
import android.widget.ImageButton;
import android.widget.TextView;

import com.firebase.ui.database.FirebaseRecyclerAdapter;
import com.firebase.ui.database.FirebaseRecyclerOptions;
import com.google.firebase.database.DatabaseReference;
import com.google.firebase.database.FirebaseDatabase;
import com.google.firebase.database.Query;

public class FirebaseSearch extends AppCompatActivity {

  private EditText mSearchField;
  private ImageButton mSearchBtn;

  private RecyclerView mResultList;

  private DatabaseReference mUserDatabase;

  @Override
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);

      mUserDatabase = FirebaseDatabase.getInstance().getReference("Users");

      mSearchField = (EditText) findViewById(R.id.search_field);

      mSearchBtn = findViewById(R.id.imageButton);
      mResultList = (RecyclerView) findViewById(R.id.view);

      mResultList.setHasFixedSize(true);
      mResultList.setLayoutManager(new LinearLayoutManager(this));

      mSearchBtn.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View view) {

              String searchText = mSearchField.getText().toString();

              firebaseUserSearch(searchText);

          }
      });

  }

  private void firebaseUserSearch(String searchText) {

      Query query = FirebaseDatabase.getInstance()
              .getReference("ipill2")
              .child("Nome")
              .startAt(searchText)
              .endAt(searchText + "\uf8ff");

      FirebaseRecyclerOptions<Users> options =
              new FirebaseRecyclerOptions.Builder<Users>()
                    .setQuery(query, Users.class)
                    .build();

    FirebaseRecyclerAdapter adapter = new FirebaseRecyclerAdapter<Users, UsersViewHolder>(options) {

        @NonNull
        @Override
        public UsersViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
            return null;
        }

        @Override
        protected void onBindViewHolder(@NonNull UsersViewHolder viewHolder, int position, @NonNull Users model) {
            viewHolder.setDetails(getApplicationContext(), model.getNome(), model.getCognome());
        }
    };

    mResultList.setAdapter(adapter);

}

// View Holder Class

public static class UsersViewHolder extends RecyclerView.ViewHolder {

    View mView;

    public UsersViewHolder(View itemView) {
        super(itemView);

        mView = itemView;

    }

    public void setDetails(Context ctx, String nome, String cognome) {

        TextView user_name = (TextView) mView.findViewById(R.id.NomeDettagli);
        TextView user_cognome = (TextView) mView.findViewById(R.id.cognomeDetail);

        user_name.setText(nome);
        user_cognome.setText(cognome);

      }

  }

}
</code>```

XML FILE

```<code><?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".FirebaseSearch">

<TextView
    android:id="@+id/heading_label"
    android:layout_width="327dp"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="43dp"
    android:layout_marginTop="38dp"
    android:layout_marginEnd="41dp"
    android:layout_marginBottom="642dp"
    android:text="Firebase Search"
    android:textSize="36sp" />

<EditText
    android:id="@+id/search_field"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="41dp"
    android:layout_marginTop="115dp"
    android:layout_marginEnd="156dp"
    android:layout_marginBottom="571dp"
    android:background="@drawable/search_layout"
    android:ems="10"
    android:inputType="textPersonName"
    android:paddingLeft="20dp"
    android:paddingTop="10dp"
    android:paddingRight="20dp"
    android:paddingBottom="10dp"
    android:text="Search here"
    android:textSize="12sp" />

<ImageButton
    android:id="@+id/imageButton"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="290dp"
    android:layout_marginTop="117dp"
    android:layout_marginEnd="73dp"
    android:layout_marginBottom="571dp"
    app:srcCompat="@drawable/ic_search_black_24dp" />

<androidx.recyclerview.widget.RecyclerView
    android:id="@+id/view"
    android:layout_width="wrap_content"
    android:layout_height="550dp"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="-4dp"
    android:layout_marginTop="181dp"
    android:layout_marginEnd="0dp"
    android:layout_marginBottom="0dp" />

 </RelativeLayout>
</code>```

Dependencies

```<code>  apply plugin: 'com.android.application'
  apply plugin: 'com.google.gms.google-services'

   android {
       compileSdkVersion 29
       buildToolsVersion "29.0.2"
       defaultConfig {
       applicationId "com.example.ipill"
       minSdkVersion 29
       targetSdkVersion 29
       versionCode 1
       versionName "1.0"
       testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }
    buildTypes {
        release {
           minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-
            rules.pro'
        }
    }
}

dependencies {
implementation fileTree(dir: 'libs', include: ['*.jar'])
implementation 'androidx.appcompat:appcompat:1.1.0'
implementation 'com.android.support:appcompat-v7:29.1.0'
implementation 'com.android.support:design:29.1.0'
implementation 'com.squareup.retrofit2:retrofit:2.3.0'
implementation 'com.squareup.picasso:picasso:2.5.2'
implementation 'com.squareup.retrofit2:converter-gson:2.3.0'

implementation 'androidx.constraintlayout:constraintlayout:1.1.3'

implementation 'com.google.android.gms:play-services:8.4.0'
implementation 'com.google.android.gms:play-services-maps:15.0.1'
implementation 'com.google.android.gms:play-services-location:15.0.1'
implementation 'com.google.android.gms:play-services-auth:9.2.1'
// Add the Firebase SDK for Google Analytics
// Add the SDKs for any other Firebase products you want to use in your app
// For example, to use Firebase Authentication and Cloud Firestore
implementation 'com.google.firebase:firebase-auth:17.0.0'
implementation 'com.google.firebase:firebase-firestore:21.3.1'
implementation 'com.google.firebase:firebase-analytics:17.2.2'
implementation 'com.google.firebase:firebase-core:17.2.0'
implementation 'com.google.firebase:firebase-database:19.2.0'
implementation 'com.google.firebase:firebase-storage:19.1.0'
testImplementation 'junit:junit:4.12'

androidTestImplementation 'androidx.test.ext:junit:1.1.1'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'

implementation 'com.google.android.gms:play-services-ads:18.3.0'
implementation 'com.google.android.gms:play-services-auth:17.0.0'

implementation 'com.github.bumptech.glide:glide:4.11.0'

annotationProcessor 'com.github.bumptech.glide:compiler:4.11.0'

implementation 'com.firebaseui:firebase-ui-database:6.2.0'

}
</code>```

THE ERROR THAT I GET

```<code>E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.ipill, PID: 11437
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.ipill/com.example.ipill.FirebaseSearch}: java.lang.NullPointerException: Attempt to invoke virtual method 'void androidx.recyclerview.widget.RecyclerView.setHasFixedSize(boolean)' on a null object reference
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3271)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3410)
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
    at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2017)
    at android.os.Handler.dispatchMessage(Handler.java:107)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7403)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:935)
 Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'void androidx.recyclerview.widget.RecyclerView.setHasFixedSize(boolean)' on a null object reference
    at com.example.ipill.FirebaseSearch.onCreate(FirebaseSearch.java:43)
    at android.app.Activity.performCreate(Activity.java:7802)
    at android.app.Activity.performCreate(Activity.java:7791)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1307)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3246)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3410)
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
    at
    android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2017)
    at android.os.Handler.dispatchMessage(Handler.java:107)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7403)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:935) enter code here
</code>```

答: Change your recyclerview id  name don't use view.  It is keyword </p>

Body:
Hi everyone I'm trying to do a realtime search from a firebase database and display the data but I get the error below every time and I can't understand how to fix it. Can anyone help me to fix it? I'm new to Android and I can't understand what's going on.  If anyone could suggest to me what I should do it would be incredible.
Thank you in advance.

Firebase Search Activity

package com.example.ipill;

import androidx.annotation.NonNull;
import androidx.appcompat.app.AppCompatActivity;
import androidx.recyclerview.widget.LinearLayoutManager;
import androidx.recyclerview.widget.RecyclerView;

import android.content.Context;
import android.os.Bundle;
import android.view.View;
import android.view.ViewGroup;
import android.widget.EditText;
import android.widget.ImageButton;
import android.widget.TextView;

import com.firebase.ui.database.FirebaseRecyclerAdapter;
import com.firebase.ui.database.FirebaseRecyclerOptions;
import com.google.firebase.database.DatabaseReference;
import com.google.firebase.database.FirebaseDatabase;
import com.google.firebase.database.Query;

public class FirebaseSearch extends AppCompatActivity {

  private EditText mSearchField;
  private ImageButton mSearchBtn;

  private RecyclerView mResultList;

  private DatabaseReference mUserDatabase;

  @Override
  protected void onCreate(Bundle savedInstanceState) {
      super.onCreate(savedInstanceState);
      setContentView(R.layout.activity_main);

      mUserDatabase = FirebaseDatabase.getInstance().getReference("Users");

      mSearchField = (EditText) findViewById(R.id.search_field);

      mSearchBtn = findViewById(R.id.imageButton);
      mResultList = (RecyclerView) findViewById(R.id.view);

      mResultList.setHasFixedSize(true);
      mResultList.setLayoutManager(new LinearLayoutManager(this));

      mSearchBtn.setOnClickListener(new View.OnClickListener() {
          @Override
          public void onClick(View view) {

              String searchText = mSearchField.getText().toString();

              firebaseUserSearch(searchText);

          }
      });

  }

  private void firebaseUserSearch(String searchText) {

      Query query = FirebaseDatabase.getInstance()
              .getReference("ipill2")
              .child("Nome")
              .startAt(searchText)
              .endAt(searchText + "\uf8ff");

      FirebaseRecyclerOptions<Users> options =
              new FirebaseRecyclerOptions.Builder<Users>()
                    .setQuery(query, Users.class)
                    .build();

    FirebaseRecyclerAdapter adapter = new FirebaseRecyclerAdapter<Users, UsersViewHolder>(options) {

        @NonNull
        @Override
        public UsersViewHolder onCreateViewHolder(@NonNull ViewGroup parent, int viewType) {
            return null;
        }

        @Override
        protected void onBindViewHolder(@NonNull UsersViewHolder viewHolder, int position, @NonNull Users model) {
            viewHolder.setDetails(getApplicationContext(), model.getNome(), model.getCognome());
        }
    };

    mResultList.setAdapter(adapter);

}

// View Holder Class

public static class UsersViewHolder extends RecyclerView.ViewHolder {

    View mView;

    public UsersViewHolder(View itemView) {
        super(itemView);

        mView = itemView;

    }

    public void setDetails(Context ctx, String nome, String cognome) {

        TextView user_name = (TextView) mView.findViewById(R.id.NomeDettagli);
        TextView user_cognome = (TextView) mView.findViewById(R.id.cognomeDetail);

        user_name.setText(nome);
        user_cognome.setText(cognome);

      }

  }

}

XML FILE

<?xml version="1.0" encoding="utf-8"?>
<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:app="http://schemas.android.com/apk/res-auto"
xmlns:tools="http://schemas.android.com/tools"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".FirebaseSearch">

<TextView
    android:id="@+id/heading_label"
    android:layout_width="327dp"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="43dp"
    android:layout_marginTop="38dp"
    android:layout_marginEnd="41dp"
    android:layout_marginBottom="642dp"
    android:text="Firebase Search"
    android:textSize="36sp" />

<EditText
    android:id="@+id/search_field"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="41dp"
    android:layout_marginTop="115dp"
    android:layout_marginEnd="156dp"
    android:layout_marginBottom="571dp"
    android:background="@drawable/search_layout"
    android:ems="10"
    android:inputType="textPersonName"
    android:paddingLeft="20dp"
    android:paddingTop="10dp"
    android:paddingRight="20dp"
    android:paddingBottom="10dp"
    android:text="Search here"
    android:textSize="12sp" />

<ImageButton
    android:id="@+id/imageButton"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="290dp"
    android:layout_marginTop="117dp"
    android:layout_marginEnd="73dp"
    android:layout_marginBottom="571dp"
    app:srcCompat="@drawable/ic_search_black_24dp" />

<androidx.recyclerview.widget.RecyclerView
    android:id="@+id/view"
    android:layout_width="wrap_content"
    android:layout_height="550dp"
    android:layout_alignParentStart="true"
    android:layout_alignParentTop="true"
    android:layout_alignParentEnd="true"
    android:layout_alignParentBottom="true"
    android:layout_marginStart="-4dp"
    android:layout_marginTop="181dp"
    android:layout_marginEnd="0dp"
    android:layout_marginBottom="0dp" />

 </RelativeLayout>

Dependencies

  apply plugin: 'com.android.application'
  apply plugin: 'com.google.gms.google-services'

   android {
       compileSdkVersion 29
       buildToolsVersion "29.0.2"
       defaultConfig {
       applicationId "com.example.ipill"
       minSdkVersion 29
       targetSdkVersion 29
       versionCode 1
       versionName "1.0"
       testInstrumentationRunner "androidx.test.runner.AndroidJUnitRunner"
    }
    buildTypes {
        release {
           minifyEnabled false
            proguardFiles getDefaultProguardFile('proguard-android-optimize.txt'), 'proguard-
            rules.pro'
        }
    }
}

dependencies {
implementation fileTree(dir: 'libs', include: ['*.jar'])
implementation 'androidx.appcompat:appcompat:1.1.0'
implementation 'com.android.support:appcompat-v7:29.1.0'
implementation 'com.android.support:design:29.1.0'
implementation 'com.squareup.retrofit2:retrofit:2.3.0'
implementation 'com.squareup.picasso:picasso:2.5.2'
implementation 'com.squareup.retrofit2:converter-gson:2.3.0'

implementation 'androidx.constraintlayout:constraintlayout:1.1.3'

implementation 'com.google.android.gms:play-services:8.4.0'
implementation 'com.google.android.gms:play-services-maps:15.0.1'
implementation 'com.google.android.gms:play-services-location:15.0.1'
implementation 'com.google.android.gms:play-services-auth:9.2.1'
// Add the Firebase SDK for Google Analytics
// Add the SDKs for any other Firebase products you want to use in your app
// For example, to use Firebase Authentication and Cloud Firestore
implementation 'com.google.firebase:firebase-auth:17.0.0'
implementation 'com.google.firebase:firebase-firestore:21.3.1'
implementation 'com.google.firebase:firebase-analytics:17.2.2'
implementation 'com.google.firebase:firebase-core:17.2.0'
implementation 'com.google.firebase:firebase-database:19.2.0'
implementation 'com.google.firebase:firebase-storage:19.1.0'
testImplementation 'junit:junit:4.12'

androidTestImplementation 'androidx.test.ext:junit:1.1.1'
androidTestImplementation 'androidx.test.espresso:espresso-core:3.2.0'

implementation 'com.google.android.gms:play-services-ads:18.3.0'
implementation 'com.google.android.gms:play-services-auth:17.0.0'

implementation 'com.github.bumptech.glide:glide:4.11.0'

annotationProcessor 'com.github.bumptech.glide:compiler:4.11.0'

implementation 'com.firebaseui:firebase-ui-database:6.2.0'

}

THE ERROR THAT I GET

E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.ipill, PID: 11437
java.lang.RuntimeException: Unable to start activity ComponentInfo{com.example.ipill/com.example.ipill.FirebaseSearch}: java.lang.NullPointerException: Attempt to invoke virtual method 'void androidx.recyclerview.widget.RecyclerView.setHasFixedSize(boolean)' on a null object reference
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3271)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3410)
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
    at android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2017)
    at android.os.Handler.dispatchMessage(Handler.java:107)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7403)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:935)
 Caused by: java.lang.NullPointerException: Attempt to invoke virtual method 'void androidx.recyclerview.widget.RecyclerView.setHasFixedSize(boolean)' on a null object reference
    at com.example.ipill.FirebaseSearch.onCreate(FirebaseSearch.java:43)
    at android.app.Activity.performCreate(Activity.java:7802)
    at android.app.Activity.performCreate(Activity.java:7791)
    at android.app.Instrumentation.callActivityOnCreate(Instrumentation.java:1307)
    at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:3246)
    at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:3410)
    at android.app.servertransaction.LaunchActivityItem.execute(LaunchActivityItem.java:83)
    at
    android.app.servertransaction.TransactionExecutor.executeCallbacks(TransactionExecutor.java:135)
    at android.app.servertransaction.TransactionExecutor.execute(TransactionExecutor.java:95)
    at android.app.ActivityThread$H.handleMessage(ActivityThread.java:2017)
    at android.os.Handler.dispatchMessage(Handler.java:107)
    at android.os.Looper.loop(Looper.java:214)
    at android.app.ActivityThread.main(ActivityThread.java:7403)
    at java.lang.reflect.Method.invoke(Native Method)
    at com.android.internal.os.RuntimeInit$MethodAndArgsCaller.run(RuntimeInit.java:492)
    at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:935) enter code here


问:
Update a table with longblobs is too slow
I have a table filled with data (about 20,000 records). I am trying to update it by the data from another table, but I have a timeout (30 seconds).
At first I tried a naive solution:

```<code>UPDATE TableWhithBlobs a
JOIN AnotherTable b on a.AnotherTableId = b.Id
SET a.SomeText= b.Description;
</code>```

This script is working much longer then 30 seconds, so I tried to reduce join:

```<code>UPDATE TableWhithBlobs a
SET a.SomeText = (select b.Description from AnotherTable b where a.AnotherTableId = b.Id);
</code>```

but this one is still very slow. Is there any cases how it could be fast?

Edit:

A bit explanation about what I'm doing. Previously, I had two tables, which in my script are called TableWhithBlobs and AnotherTable. In table TableWhithBlobs, a link to table AnotherTable was stored, but this link was not a real foreign key, it was just a guid from table AnotherTable. And there is a Unique key constraint for this reference in TableWhithBlobs for this guid. I decided to fix this, remove the old field from table TableWhithBlobs and add a normal foreign key to it (using the primary ID from AnotherTable). The script from the question just adds the correct data to this new field. After that, I delete old guid reference and add a new foreign key constraint. Everything works fine in the small amount of data in TableWhithBlobs, but on QA database with 20000 rows its extremely slow.

Update

SHOW CREATE TABLE TableWhithBlobs;

```<code>CREATE TABLE `TableWhithBlobs` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`AnotherTableId` char(36) CHARACTER SET ascii NOT NULL,
`ChunkNumber` bigint(20) NOT NULL,
`Content` longblob NOT NULL,
`SomeText` bigint(20) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `AnotherTableId` (`AnotherTableId`,`ChunkNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
</code>```

SHOW CREATE TABLE AnotherTable ;

```<code>CREATE TABLE `AnotherTable` (
`Description` bigint(20) NOT NULL AUTO_INCREMENT,
`Id` char(36) CHARACTER SET ascii NOT NULL,
`Length` bigint(20) NOT NULL,
`ContentDigest` char(68) CHARACTER SET ascii NOT NULL,
`ContentAndMetadataDigest` char(68) CHARACTER SET ascii NOT NULL,
`Status` smallint(6) NOT NULL,
`ChunkStartNumber` bigint(20) NOT NULL DEFAULT '0',
`IsTestData` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`Description`),
UNIQUE KEY `Id` (`Id`),
UNIQUE KEY `ContentAndMetadataDigest` (`ContentAndMetadataDigest`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
</code>```

PS. Column names may look weird because i want to hide the actual production scheme names.

innodb_buffer_pool_size is 134217728, RAM is 4Gb

Result of

```<code>explain UPDATE TableWhithBlobs a JOIN AnotherTable b on a.AnotherTableId =
b.Id SET a.SomeText= b.Description;
</code>```

Version: mysql  Ver 14.14 Distrib 5.7.21-20, for debian-linux-gnu (x86_64) using  6.3

答:
Some thoughts, none of which jump out as "the answer":

Increase innodb_buffer_pool_size to 1500M, assuming this does not lead to swapping.

Step back and look at "why" the BIGINT needs to be copied over so often.  And whether "all" rows need updating.

Put the LONGBLOB into another table in parallel with the current one.  That will add a JOIN for the cases when you need to fetch the blob, but may keep it out of the way for the current query.  (I would not expect the blob to be "in the way", but apparently it is.)

What is in the blob?  In some situations, it is better to have the blob in a file.  A prime example is an image for a web site -- it could be accessed via http's .

Increase the timeout -- but this just "sweeps the problem under the rug" and probably leads to 30+ second delays in other things that are waiting for it.  I don't recognize 30 seconds as a timeout amount.  Look through SHOW VARIABLES LIKE '%out';  Try increasing any that are 30.

Do the update piecemeal -- but would this have other implications?  (Anyway, Luuk should carry this option forward.)

Body:
I have a table filled with data (about 20,000 records). I am trying to update it by the data from another table, but I have a timeout (30 seconds).
At first I tried a naive solution:

UPDATE TableWhithBlobs a
JOIN AnotherTable b on a.AnotherTableId = b.Id
SET a.SomeText= b.Description;

This script is working much longer then 30 seconds, so I tried to reduce join:

UPDATE TableWhithBlobs a
SET a.SomeText = (select b.Description from AnotherTable b where a.AnotherTableId = b.Id);

but this one is still very slow. Is there any cases how it could be fast?

Edit:

A bit explanation about what I'm doing. Previously, I had two tables, which in my script are called TableWhithBlobs and AnotherTable. In table TableWhithBlobs, a link to table AnotherTable was stored, but this link was not a real foreign key, it was just a guid from table AnotherTable. And there is a Unique key constraint for this reference in TableWhithBlobs for this guid. I decided to fix this, remove the old field from table TableWhithBlobs and add a normal foreign key to it (using the primary ID from AnotherTable). The script from the question just adds the correct data to this new field. After that, I delete old guid reference and add a new foreign key constraint. Everything works fine in the small amount of data in TableWhithBlobs, but on QA database with 20000 rows its extremely slow.

Update

SHOW CREATE TABLE TableWhithBlobs;

CREATE TABLE `TableWhithBlobs` (
`Id` bigint(20) NOT NULL AUTO_INCREMENT,
`AnotherTableId` char(36) CHARACTER SET ascii NOT NULL,
`ChunkNumber` bigint(20) NOT NULL,
`Content` longblob NOT NULL,
`SomeText` bigint(20) NOT NULL,
PRIMARY KEY (`Id`),
UNIQUE KEY `AnotherTableId` (`AnotherTableId`,`ChunkNumber`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

SHOW CREATE TABLE AnotherTable ;

CREATE TABLE `AnotherTable` (
`Description` bigint(20) NOT NULL AUTO_INCREMENT,
`Id` char(36) CHARACTER SET ascii NOT NULL,
`Length` bigint(20) NOT NULL,
`ContentDigest` char(68) CHARACTER SET ascii NOT NULL,
`ContentAndMetadataDigest` char(68) CHARACTER SET ascii NOT NULL,
`Status` smallint(6) NOT NULL,
`ChunkStartNumber` bigint(20) NOT NULL DEFAULT '0',
`IsTestData` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`Description`),
UNIQUE KEY `Id` (`Id`),
UNIQUE KEY `ContentAndMetadataDigest` (`ContentAndMetadataDigest`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1

PS. Column names may look weird because i want to hide the actual production scheme names.

innodb_buffer_pool_size is 134217728, RAM is 4Gb

Result of

explain UPDATE TableWhithBlobs a JOIN AnotherTable b on a.AnotherTableId =
b.Id SET a.SomeText= b.Description;

Version: mysql  Ver 14.14 Distrib 5.7.21-20, for debian-linux-gnu (x86_64) using  6.3

Body:
Some thoughts, none of which jump out as "the answer":

Increase innodb_buffer_pool_size to 1500M, assuming this does not lead to swapping.

Step back and look at "why" the BIGINT needs to be copied over so often.  And whether "all" rows need updating.

Put the LONGBLOB into another table in parallel with the current one.  That will add a JOIN for the cases when you need to fetch the blob, but may keep it out of the way for the current query.  (I would not expect the blob to be "in the way", but apparently it is.)

What is in the blob?  In some situations, it is better to have the blob in a file.  A prime example is an image for a web site -- it could be accessed via http's <img...>.

Increase the timeout -- but this just "sweeps the problem under the rug" and probably leads to 30+ second delays in other things that are waiting for it.  I don't recognize 30 seconds as a timeout amount.  Look through SHOW VARIABLES LIKE '%out';  Try increasing any that are 30.

Do the update piecemeal -- but would this have other implications?  (Anyway, Luuk should carry this option forward.)