问:
Select multiple aggregation result from the same subquery
I find myself doing statistics like this more and more often - for every record in tbl1, use it as condition to scan tbl2 and do aggregation on tbl2, return the aggregated results. This is easy with sub-query.

But I need to do multiple aggregation in one pass in the same subquery instead of scanning multiple times.

For example, here is some sample data for analysis:

```<code>SELECT  to_timestamp((random()*999999)) evt_ts,
    evt_id
    INTO temp.test_so_44410354
FROM
    (
        SELECT *, (random()*2000)::int AS idx
            FROM generate_series( 1, 1000) AS evt_id
            ORDER BY idx
    ) AS t

evt_ts              |evt_id |
--------------------|-------|
1970-01-01 08:13:15 |109    |
1970-01-01 08:17:22 |762    |
1970-01-01 08:19:28 |630    |
1970-01-01 08:29:34 |429    |
1970-01-01 08:48:28 |70     |
</code>```

To see how events distribute over an given time frame,
current approach & query plan & desired output :

```<code>With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt
,(
    SELECT count(evt_ts)
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
) as ecnt
,(
    SELECT sum(evt_id) -- should be array_agg, sum is locate to the difference in the results.
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
) as eids
FROM wcal

QUERY PLAN                                                                                |
----------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..21314.08 rows=1000 width=28)                                         |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12)                  |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.63..10.64 rows=1 width=8)                                               |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=8)            |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))     |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0)     |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval))) |
  SubPlan 3                                                                                |
    ->  Aggregate  (cost=10.63..10.64 rows=1 width=8)                                               |
    ...

ts                  |dt         |ecnt |eids  |
--------------------|-----------|-----|------|
1970-01-01 00:00:00 |1970-01-01 |0    |      |
1970-01-02 00:00:00 |1970-01-02 |68   |36156 |
1970-01-03 00:00:00 |1970-01-03 |85   |42103 |
1970-01-04 00:00:00 |1970-01-04 |94   |47092 |
1970-01-05 00:00:00 |1970-01-05 |74   |36276 |
....
</code>```

There are 2 subplans, it seems pg will have to scan table evt_log twice to do the same aggregation twice. I'd would like avoid this. since the aggregation conditions are exactly the same and it maybe be much faster if it can do many statistical aggregation in one pass. Logs can grow very big, the processing will take quite some time.

Feedback on proposed solution I
nested join is not working as expected:

```<code>With wcal AS (
SELECT ts, ts::date as dt
FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
, ag AS (
    SELECT
        count(evt_ts) AS ecnt
      , sum(evt_id) AS eids
    FROM temp.test_so_44410354 as el
    JOIN wcal ON ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
)
SELECT ts, dt, ag.ecnt, eids
FROM wcal
JOIN ag ON true

QUERY PLAN                                                                                |
----------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=4239.57..4269.59 rows=1000 width=28)                                                               |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12)                                    |
  CTE ag                                                                                |
    ->  Aggregate  (cost=4227.06..4227.07 rows=1 width=16)                                                            |
          ->  Nested Loop  (cost=0.28..3671.50 rows=111111 width=12)                                                  |
                ->  CTE Scan on wcal wcal_1  (cost=0.00..20.00 rows=1000 width=8)                                     |
                ->  Index Scan using test_so_44410354_idx on test_so_44410354 el  (cost=0.28..2.54 rows=111 width=12) |
                      Index Cond: ((evt_ts < wcal_1.ts) AND (evt_ts > (wcal_1.ts - '1 day'::interval)))               |
  ->  CTE Scan on ag  (cost=0.00..0.02 rows=1 width=16)                                                               |
  ->  CTE Scan on wcal  (cost=0.00..20.00 rows=1000 width=12)                                                         |

ts                  |dt         |ecnt |eids   |
--------------------|-----------|-----|-------|
1970-01-01 00:00:00 |1970-01-01 |920  |464436 |
1970-01-04 00:00:00 |1970-01-04 |920  |464436 |
1970-01-05 00:00:00 |1970-01-05 |920  |464436 |
1970-01-06 00:00:00 |1970-01-06 |920  |464436 |
1970-01-07 00:00:00 |1970-01-07 |920  |464436 |
....
</code>```

Feedack on proposed solution II:
The writing are different but the query plan are basically the same with 2 sub plan.

```<code>DROP TYPE temp.my_agg ;
CREATE TYPE temp.my_agg AS ( ecnt int, eids int );
EXPLAIN
With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt
,(
    SELECT (count(evt_ts),sum(evt_id))::temp.my_agg
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
).*
FROM wcal

QUERY PLAN                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..21349.08 rows=1000 width=20) (actual time=0.182..3.518 rows=12 loops=1)                                      |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.057..0.083 rows=12 loops=1)               |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.64..10.66 rows=1 width=32) (actual time=0.140..0.140 rows=1 loops=12)                                           |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.049..0.083 rows=77 loops=12)       |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
                Heap Blocks: exact=66                                                                                |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.037..0.037 rows=77 loops=12) |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
  SubPlan 3                                                                                |
    ...
Planning time: 0.548 ms                                                                                |
Execution time: 3.748 ms                                                                                |
</code>```

For proposed solution II, if the aggregation is returned as record, the query plan will look like below:
Which roughly doubled the speed c.f. previous query. (but record type element is not desired as final result.)

```<code>QUERY PLAN                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..10690.79 rows=1000 width=44) (actual time=0.147..1.979 rows=12 loops=1)                                      |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.057..0.082 rows=12 loops=1)               |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.64..10.66 rows=1 width=32) (actual time=0.151..0.151 rows=1 loops=12)                                           |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.054..0.089 rows=77 loops=12)       |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
                Heap Blocks: exact=66                                                                                |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.040..0.040 rows=77 loops=12) |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
Planning time: 0.381 ms                                                                                |
Execution time: 2.147 ms                                                                                |
</code>```

The LATERAL join solution works just as expected. Thanks.

```<code>EXPLAIN ANALYZE
With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt, p.*
FROM wcal
     CROSS JOIN LATERAL (
        SELECT count(evt_ts), sum(evt_id)
        FROM temp.test_so_44410354 as el
        WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
    ) p

QUERY PLAN                                                                                |
------------------------------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=23.15..10705.79 rows=1000 width=28) (actual time=0.130..1.611 rows=12 loops=1)                                         |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.054..0.075 rows=12 loops=1)             |
  ->  CTE Scan on wcal  (cost=0.00..20.00 rows=1000 width=12) (actual time=0.061..0.099 rows=12 loops=1)                                  |
  ->  Aggregate  (cost=10.64..10.65 rows=1 width=16) (actual time=0.123..0.123 rows=1 loops=12)                                           |
        ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.044..0.074 rows=77 loops=12)       |
              Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
              Heap Blocks: exact=66                                                                                |
              ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.033..0.033 rows=77 loops=12) |
                    Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
Planning time: 0.399 ms                                                                                |
Execution time: 1.778 ms                                                                                |

ts                  |dt         |count |sum   |
--------------------|-----------|------|------|
1970-01-01 00:00:00 |1970-01-01 |0     |      |
1970-01-02 00:00:00 |1970-01-02 |68    |36156 |
1970-01-03 00:00:00 |1970-01-03 |85    |42103 |
1970-01-04 00:00:00 |1970-01-04 |94    |47092 |
1970-01-05 00:00:00 |1970-01-05 |74    |36276 |
1970-01-06 00:00:00 |1970-01-06 |96    |43797 |
</code>```

答:
smth like?

```<code>With wcal AS (
    SELECT ts, ts::date as dt,
        extract( week from ts) as wk,
        extract( month from ts ) as mo,
        extract( quater from ts ) as qtr,
    FROM generate_series( t1, t2, tspan )
)
, ag as (
    SELECT count(evt_ts) ecnt,  array_aggr(evt_id) eids
    FROM ev_log as el
    JOIN wcal on (el.ts < wcal.ts AND el.ts < wcal.ts - tspan)
)
SELECT ts, dt, wk, mo, qtr, ecnt, eids
FROM wcal
JOIN ag on true
</code>```

I'm not sure about the join conditions - you need to put the whole query to the OP to be able to guess

updated

according to Abelisto notices - thank you

Body:
I find myself doing statistics like this more and more often - for every record in tbl1, use it as condition to scan tbl2 and do aggregation on tbl2, return the aggregated results. This is easy with sub-query.

But I need to do multiple aggregation in one pass in the same subquery instead of scanning multiple times.

For example, here is some sample data for analysis:

SELECT  to_timestamp((random()*999999)) evt_ts,
    evt_id
    INTO temp.test_so_44410354
FROM
    (
        SELECT *, (random()*2000)::int AS idx
            FROM generate_series( 1, 1000) AS evt_id
            ORDER BY idx
    ) AS t

evt_ts              |evt_id |
--------------------|-------|
1970-01-01 08:13:15 |109    |
1970-01-01 08:17:22 |762    |
1970-01-01 08:19:28 |630    |
1970-01-01 08:29:34 |429    |
1970-01-01 08:48:28 |70     |

To see how events distribute over an given time frame,
current approach & query plan & desired output :

With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt
,(
    SELECT count(evt_ts)
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
) as ecnt
,(
    SELECT sum(evt_id) -- should be array_agg, sum is locate to the difference in the results.
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
) as eids
FROM wcal

QUERY PLAN                                                                                |
----------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..21314.08 rows=1000 width=28)                                         |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12)                  |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.63..10.64 rows=1 width=8)                                               |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=8)            |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))     |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0)     |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval))) |
  SubPlan 3                                                                                |
    ->  Aggregate  (cost=10.63..10.64 rows=1 width=8)                                               |
    ...

ts                  |dt         |ecnt |eids  |
--------------------|-----------|-----|------|
1970-01-01 00:00:00 |1970-01-01 |0    |      |
1970-01-02 00:00:00 |1970-01-02 |68   |36156 |
1970-01-03 00:00:00 |1970-01-03 |85   |42103 |
1970-01-04 00:00:00 |1970-01-04 |94   |47092 |
1970-01-05 00:00:00 |1970-01-05 |74   |36276 |
....

There are 2 subplans, it seems pg will have to scan table evt_log twice to do the same aggregation twice. I'd would like avoid this. since the aggregation conditions are exactly the same and it maybe be much faster if it can do many statistical aggregation in one pass. Logs can grow very big, the processing will take quite some time.

Feedback on proposed solution I
nested join is not working as expected:

With wcal AS (
SELECT ts, ts::date as dt
FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
, ag AS (
    SELECT
        count(evt_ts) AS ecnt
      , sum(evt_id) AS eids
    FROM temp.test_so_44410354 as el
    JOIN wcal ON ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
)
SELECT ts, dt, ag.ecnt, eids
FROM wcal
JOIN ag ON true

QUERY PLAN                                                                                |
----------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=4239.57..4269.59 rows=1000 width=28)                                                               |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12)                                    |
  CTE ag                                                                                |
    ->  Aggregate  (cost=4227.06..4227.07 rows=1 width=16)                                                            |
          ->  Nested Loop  (cost=0.28..3671.50 rows=111111 width=12)                                                  |
                ->  CTE Scan on wcal wcal_1  (cost=0.00..20.00 rows=1000 width=8)                                     |
                ->  Index Scan using test_so_44410354_idx on test_so_44410354 el  (cost=0.28..2.54 rows=111 width=12) |
                      Index Cond: ((evt_ts < wcal_1.ts) AND (evt_ts > (wcal_1.ts - '1 day'::interval)))               |
  ->  CTE Scan on ag  (cost=0.00..0.02 rows=1 width=16)                                                               |
  ->  CTE Scan on wcal  (cost=0.00..20.00 rows=1000 width=12)                                                         |

ts                  |dt         |ecnt |eids   |
--------------------|-----------|-----|-------|
1970-01-01 00:00:00 |1970-01-01 |920  |464436 |
1970-01-04 00:00:00 |1970-01-04 |920  |464436 |
1970-01-05 00:00:00 |1970-01-05 |920  |464436 |
1970-01-06 00:00:00 |1970-01-06 |920  |464436 |
1970-01-07 00:00:00 |1970-01-07 |920  |464436 |
....

Feedack on proposed solution II:
The writing are different but the query plan are basically the same with 2 sub plan.

DROP TYPE temp.my_agg ;
CREATE TYPE temp.my_agg AS ( ecnt int, eids int );
EXPLAIN
With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt
,(
    SELECT (count(evt_ts),sum(evt_id))::temp.my_agg
    FROM temp.test_so_44410354 as el
    WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
).*
FROM wcal

QUERY PLAN                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..21349.08 rows=1000 width=20) (actual time=0.182..3.518 rows=12 loops=1)                                      |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.057..0.083 rows=12 loops=1)               |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.64..10.66 rows=1 width=32) (actual time=0.140..0.140 rows=1 loops=12)                                           |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.049..0.083 rows=77 loops=12)       |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
                Heap Blocks: exact=66                                                                                |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.037..0.037 rows=77 loops=12) |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
  SubPlan 3                                                                                |
    ...
Planning time: 0.548 ms                                                                                |
Execution time: 3.748 ms                                                                                |

For proposed solution II, if the aggregation is returned as record, the query plan will look like below:
Which roughly doubled the speed c.f. previous query. (but record type element is not desired as final result.)

QUERY PLAN                                                                                |
--------------------------------------------------------------------------------------------------------------------------------------------|
CTE Scan on wcal  (cost=12.50..10690.79 rows=1000 width=44) (actual time=0.147..1.979 rows=12 loops=1)                                      |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.057..0.082 rows=12 loops=1)               |
  SubPlan 2                                                                                |
    ->  Aggregate  (cost=10.64..10.66 rows=1 width=32) (actual time=0.151..0.151 rows=1 loops=12)                                           |
          ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.054..0.089 rows=77 loops=12)       |
                Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
                Heap Blocks: exact=66                                                                                |
                ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.040..0.040 rows=77 loops=12) |
                      Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
Planning time: 0.381 ms                                                                                |
Execution time: 2.147 ms                                                                                |

The LATERAL join solution works just as expected. Thanks.

EXPLAIN ANALYZE
With wcal AS (
    SELECT ts, ts::date as dt
    FROM generate_series( '1970-01-01 0:0:0', '1970-01-12 0:0:0', INTERVAL '1 day' ) AS ts
)
SELECT ts, dt, p.*
FROM wcal
     CROSS JOIN LATERAL (
        SELECT count(evt_ts), sum(evt_id)
        FROM temp.test_so_44410354 as el
        WHERE ( el.evt_ts < wcal.ts AND el.evt_ts > wcal.ts - INTERVAL '1 day'  )
    ) p

QUERY PLAN                                                                                |
------------------------------------------------------------------------------------------------------------------------------------------|
Nested Loop  (cost=23.15..10705.79 rows=1000 width=28) (actual time=0.130..1.611 rows=12 loops=1)                                         |
  CTE wcal                                                                                |
    ->  Function Scan on generate_series ts  (cost=0.00..12.50 rows=1000 width=12) (actual time=0.054..0.075 rows=12 loops=1)             |
  ->  CTE Scan on wcal  (cost=0.00..20.00 rows=1000 width=12) (actual time=0.061..0.099 rows=12 loops=1)                                  |
  ->  Aggregate  (cost=10.64..10.65 rows=1 width=16) (actual time=0.123..0.123 rows=1 loops=12)                                           |
        ->  Bitmap Heap Scan on test_so_44410354 el  (cost=4.33..10.62 rows=5 width=12) (actual time=0.044..0.074 rows=77 loops=12)       |
              Recheck Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                             |
              Heap Blocks: exact=66                                                                                |
              ->  Bitmap Index Scan on test_so_44410354_idx  (cost=0.00..4.33 rows=5 width=0) (actual time=0.033..0.033 rows=77 loops=12) |
                    Index Cond: ((evt_ts < wcal.ts) AND (evt_ts > (wcal.ts - '1 day'::interval)))                                         |
Planning time: 0.399 ms                                                                                |
Execution time: 1.778 ms                                                                                |

ts                  |dt         |count |sum   |
--------------------|-----------|------|------|
1970-01-01 00:00:00 |1970-01-01 |0     |      |
1970-01-02 00:00:00 |1970-01-02 |68    |36156 |
1970-01-03 00:00:00 |1970-01-03 |85    |42103 |
1970-01-04 00:00:00 |1970-01-04 |94    |47092 |
1970-01-05 00:00:00 |1970-01-05 |74    |36276 |
1970-01-06 00:00:00 |1970-01-06 |96    |43797 |

Body:
smth like?

With wcal AS (
    SELECT ts, ts::date as dt,
        extract( week from ts) as wk,
        extract( month from ts ) as mo,
        extract( quater from ts ) as qtr,
    FROM generate_series( t1, t2, tspan )
)
, ag as (
    SELECT count(evt_ts) ecnt,  array_aggr(evt_id) eids
    FROM ev_log as el
    JOIN wcal on (el.ts < wcal.ts AND el.ts < wcal.ts - tspan)
)
SELECT ts, dt, wk, mo, qtr, ecnt, eids
FROM wcal
JOIN ag on true

I'm not sure about the join conditions - you need to put the whole query to the OP to be able to guess

updated

according to Abelisto notices - thank you


来源: stackexchange

问:
Codename One - Externalizable object not supported in web service
I have created a CN1 web service which some custom objects that I want to externalize in order to send over the network. I read through several articles on how to create the web service and how to work with the CN1 Externalizable interface.

This works well for web service methods that return a custom externalizable object, however the only indicator that I have is that a method which takes an externalizable object as an argument, I get the following error:

```<code>SCHWERWIEGEND: Servlet.service() for servlet  [CN1WebServiceServlet]
in context with path [/<myPath>] threw exception
java.io.IOException: Object type not supported: Post
</code>```

The object is properly registered with the Util class, as changing either the object ID or commenting out the register call will cause a null pointer instead of the IO exception.

The Post class looks like this (simplified to the minimum which already fails):

```<code>public class Post implements Externalizable {
public int postid;
public int userid;

// default constructor needed for web service marshalling
public Post() {

}

@Override
public int getVersion() {
    return 1;
}

@Override
public void externalize(DataOutputStream out) throws IOException {
    Util.writeUTF("" + postid, out);
    Util.writeUTF("" + userid, out);
}

@Override
public void internalize(int version, DataInputStream in) throws IOException {
    this.postid = Integer.parseInt(Util.readUTF(in));
    this.userid = Integer.parseInt(Util.readUTF(in));
}

@Override
public String getObjectId() {
    return "Post";
}
</code>```

Note that this Post object works well when I call a web service method which returns a post object, but not when I send a Post object to the web service:

```<code>// works
public static com.codename1.io.Externalizable getPostDetails(int postid) {
   return getPostDetails(postid);
}

// fails
public static void sendPost(com.codename1.io.Externalizable post) {
    sendPost(post);
}
</code>```

I am at a loss of what I missed here.

Thanks and best regards

答:
Thanks a lot Shai! My mistake was to assume that registering the externalizable object on one side only. But of course it needs to be registered wherever it is going to be internalized, so in this case on my server.

Solution:
Within the "CN1WebServiceServlet" (not the ProxyServer class where the rest of the code has to be completed), call Util.register("Post", Post.class);

```<code>      if(methodName.equals("sendPost")) {
            Util.register("Post", Post.class); // this is a my insertedline, rest is generated
            Object[] args = ProxyServerHelper.readMethodArguments(di, def_sendPost);
            WebServiceProxyServer.sendPost((com.codename1.io.Externalizable)args[0]);
            ProxyServerHelper.writeResponse(response, def_sendPost);
            return;
        }
</code>```

ContentLicense: CC BY-SA 3.0

Body:
I have created a CN1 web service which some custom objects that I want to externalize in order to send over the network. I read through several articles on how to create the web service and how to work with the CN1 Externalizable interface.

This works well for web service methods that return a custom externalizable object, however the only indicator that I have is that a method which takes an externalizable object as an argument, I get the following error:

SCHWERWIEGEND: Servlet.service() for servlet  [CN1WebServiceServlet]
in context with path [/<myPath>] threw exception
java.io.IOException: Object type not supported: Post

The object is properly registered with the Util class, as changing either the object ID or commenting out the register call will cause a null pointer instead of the IO exception.

The Post class looks like this (simplified to the minimum which already fails):

public class Post implements Externalizable {
public int postid;
public int userid;

// default constructor needed for web service marshalling
public Post() {

}

@Override
public int getVersion() {
    return 1;
}

@Override
public void externalize(DataOutputStream out) throws IOException {
    Util.writeUTF("" + postid, out);
    Util.writeUTF("" + userid, out);
}

@Override
public void internalize(int version, DataInputStream in) throws IOException {
    this.postid = Integer.parseInt(Util.readUTF(in));
    this.userid = Integer.parseInt(Util.readUTF(in));
}

@Override
public String getObjectId() {
    return "Post";
}

Note that this Post object works well when I call a web service method which returns a post object, but not when I send a Post object to the web service:

// works
public static com.codename1.io.Externalizable getPostDetails(int postid) {
   return getPostDetails(postid);
}

// fails
public static void sendPost(com.codename1.io.Externalizable post) {
    sendPost(post);
}

I am at a loss of what I missed here.

Thanks and best regards

Body:
Thanks a lot Shai! My mistake was to assume that registering the externalizable object on one side only. But of course it needs to be registered wherever it is going to be internalized, so in this case on my server.

Solution:
Within the "CN1WebServiceServlet" (not the ProxyServer class where the rest of the code has to be completed), call Util.register("Post", Post.class);

      if(methodName.equals("sendPost")) {
            Util.register("Post", Post.class); // this is a my insertedline, rest is generated
            Object[] args = ProxyServerHelper.readMethodArguments(di, def_sendPost);
            WebServiceProxyServer.sendPost((com.codename1.io.Externalizable)args[0]);
            ProxyServerHelper.writeResponse(response, def_sendPost);
            return;
        }


问:
Android Shared Preferences Demo
create file with bellow name

how to use

public class AppPreferences

{

```<code>public static final String PREF_NAME = "XYZ";

@SuppressWarnings("deprecation")
public static final int MODE = Context.MODE_PRIVATE;

 public static final String ISLOGIN = "islogin";

public static void writeBoolean(Context context, String key, boolean value)
{
    getEditor(context).putBoolean(key, value).commit();
}

public static boolean readBoolean(Context context, String key,
                                  boolean defValue) {
    return getPreferences(context).getBoolean(key, defValue);
}

public static void writeInteger(Context context, String key, int value) {
    getEditor(context).putInt(key, value).commit();

}

public static int readInteger(Context context, String key, int defValue) {
    return getPreferences(context).getInt(key, defValue);
}

public static void writeString(Context context, String key, String value) {
    getEditor(context).putString(key, value).commit();

}

public static String readString(Context context, String key, String defValue) {
    return getPreferences(context).getString(key, defValue);
}

public static void writeFloat(Context context, String key, float value) {
    getEditor(context).putFloat(key, value).commit();
}

public static float readFloat(Context context, String key, float defValue) {
    return getPreferences(context).getFloat(key, defValue);
}

public static void writeLong(Context context, String key, long value) {
    getEditor(context).putLong(key, value).commit();
}

public static long readLong(Context context, String key, long defValue) {
    return getPreferences(context).getLong(key, defValue);
}

public static SharedPreferences getPreferences(Context context) {
    return context.getSharedPreferences(PREF_NAME, MODE);
}

public static SharedPreferences.Editor getEditor(Context context) {
    return getPreferences(context).edit();
}
</code>```

}

how to use :

1) Get Data:
 boolean islogin = AppPreferences.readBoolean(Splash_screen.this,AppPreferences.ISLOGIN,false);

2) Put Data :
AppPreferences.writeBoolean(LoginActivity.this,AppPreferences.ISLOGIN,true);

答: this should work fine ,would you explain whats the problem </p>

Title: Android Shared Preferences Demo

Body:
create file with bellow name

how to use

public class AppPreferences

{

public static final String PREF_NAME = "XYZ";

@SuppressWarnings("deprecation")
public static final int MODE = Context.MODE_PRIVATE;

 public static final String ISLOGIN = "islogin";

public static void writeBoolean(Context context, String key, boolean value)
{
    getEditor(context).putBoolean(key, value).commit();
}

public static boolean readBoolean(Context context, String key,
                                  boolean defValue) {
    return getPreferences(context).getBoolean(key, defValue);
}

public static void writeInteger(Context context, String key, int value) {
    getEditor(context).putInt(key, value).commit();

}

public static int readInteger(Context context, String key, int defValue) {
    return getPreferences(context).getInt(key, defValue);
}

public static void writeString(Context context, String key, String value) {
    getEditor(context).putString(key, value).commit();

}

public static String readString(Context context, String key, String defValue) {
    return getPreferences(context).getString(key, defValue);
}

public static void writeFloat(Context context, String key, float value) {
    getEditor(context).putFloat(key, value).commit();
}

public static float readFloat(Context context, String key, float defValue) {
    return getPreferences(context).getFloat(key, defValue);
}

public static void writeLong(Context context, String key, long value) {
    getEditor(context).putLong(key, value).commit();
}

public static long readLong(Context context, String key, long defValue) {
    return getPreferences(context).getLong(key, defValue);
}

public static SharedPreferences getPreferences(Context context) {
    return context.getSharedPreferences(PREF_NAME, MODE);
}

public static SharedPreferences.Editor getEditor(Context context) {
    return getPreferences(context).edit();
}

}

how to use :

1) Get Data:
 boolean islogin = AppPreferences.readBoolean(Splash_screen.this,AppPreferences.ISLOGIN,false);

2) Put Data :
AppPreferences.writeBoolean(LoginActivity.this,AppPreferences.ISLOGIN,true);


问:
Base64 Decode : Specific String Incorrect Padding (with correct padding)
I am trying to Base64 decode a string (into bytes) using Python's base64.b64decode(str) method:

46oWrWpy2gTEGwNnN6Ayy

and I am ensuring it has a multiple of 4 ='s for padding or out of frustration any of these:

46oWrWpy2gTEGwNnN6Ayy=

46oWrWpy2gTEGwNnN6Ayy==

46oWrWpy2gTEGwNnN6Ayy===

46oWrWpy2gTEGwNnN6Ayy==================================================

and yet I still get "Incorrect Padding" on Python v3.6.1. Other strings are fine.

I show a colleague, he tries on Python 2 and observes the same response.

I note removing the first "4" is enough to ensure the Base64 decode works.

I have skim read Python's docs (noting casefold doesn't apply for Base64) and haven't yet ventured further into RFC3548 but wondered if someone else had encountered something similar before. Anyone have any clues :)? Surely this can't be a bug in Python's Base64 decoder?

答:
Worked it out.

Each character of Base64 text is 6 bits of the raw's 8 bits. If a character is mid-way through the raw's bytes then you are missing some remaining bits. The Wikipedia article (and many online answers) seems to use padding as interchangeable for a '0' byte which is not the case (in the Base64 dictionary it should be encoded as an A).

Padding is not interchangeable for missing data.

```<code>#!/usr/bin/env python3

# We use hexlify for debugging.
import binascii

# We use the Base64 library.
import base64

# Base64 works on multiples of 4 characters..
# ..Sometimes we get 3/2/1 characters and it might be midway through another.
def relaxed_decode_base64(data):

 # If there is already padding we strim it as we calculate padding ourselves.
 if '=' in data:
  data = data[:data.index('=')]

 # We need to add padding, how many bytes are missing.
 missing_padding = len(data) % 4

 # We would be mid-way through a byte.
 if missing_padding == 1:
  data += 'A=='
 # Jut add on the correct length of padding.
 elif missing_padding == 2:
  data += '=='
 elif missing_padding == 3:
  data += '='

 # Actually perform the Base64 decode.
 return base64.b64decode(data)

# Debugging
print(str(relaxed_decode_base64('46oWrWpy2gTEGwNnN6Ayy')) + '\n')

testString = ''

for count in range(0, 1024):
 testString += '/'
 print(str(len(testString)) + ' - ' + testString)
 print(binascii.hexlify(relaxed_decode_base64(testString)))
 input()
</code>```

Body:
Worked it out.

Each character of Base64 text is 6 bits of the raw's 8 bits. If a character is mid-way through the raw's bytes then you are missing some remaining bits. The Wikipedia article (and many online answers) seems to use padding as interchangeable for a '0' byte which is not the case (in the Base64 dictionary it should be encoded as an A).

Padding is not interchangeable for missing data.

#!/usr/bin/env python3

# We use hexlify for debugging.
import binascii

# We use the Base64 library.
import base64

# Base64 works on multiples of 4 characters..
# ..Sometimes we get 3/2/1 characters and it might be midway through another.
def relaxed_decode_base64(data):

 # If there is already padding we strim it as we calculate padding ourselves.
 if '=' in data:
  data = data[:data.index('=')]

 # We need to add padding, how many bytes are missing.
 missing_padding = len(data) % 4

 # We would be mid-way through a byte.
 if missing_padding == 1:
  data += 'A=='
 # Jut add on the correct length of padding.
 elif missing_padding == 2:
  data += '=='
 elif missing_padding == 3:
  data += '='

 # Actually perform the Base64 decode.
 return base64.b64decode(data)

# Debugging
print(str(relaxed_decode_base64('46oWrWpy2gTEGwNnN6Ayy')) + '\n')

testString = ''

for count in range(0, 1024):
 testString += '/'
 print(str(len(testString)) + ' - ' + testString)
 print(binascii.hexlify(relaxed_decode_base64(testString)))
 input()


问:
Accessing a spreadsheet on Google Sheets using python3

I'm trying to read from a personal spreadsheet using the google api (v4) for spreadsheets.

I copied the code from the example google is providing while changing the spreadsheet id, range name and scope.

No matter what I do (make the spreadsheet public etc) I'm getting an HttpError: 404 Requested entity was not found.

My Code:

```<code>import httplib2
import os

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly',
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive',
        'https://www.googleapis.com/auth/drive.readonly']
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'python'

def get_credentials():
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
            'sheets.googleapis.com-python.json')

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        credentials = tools.run_flow(flow, store, None)
    return credentials

def main():
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
            'version=v4')
    service = discovery.build('sheets', 'v4', http = http,
            discoveryServiceUrl = discoveryUrl)
    spreadsheetId = 'ID'
    rangeName = 'RANGE'
    result = service.spreadsheets().values().get(
            spreadsheetId = spreadsheetId, range = rangeName).execute()
</code>```

答:
Seeing  quite a lot of boilerplate code in the question and the previous answer for a seemingly simple task I would like to share my recipe for this using  pygsheets.

To be able to access Google Sheets API from Python script, I have registered on Google API Dashboard and have chosen Signed Credentials option described in Authorizing pygsheets.

I have downloaded a json file with credentials from Google API and saved it in the same directory as my Python script. The json file contains a dedicated email address that looks like

x-...updater@x...updater.iam.gserviceaccount.com

For the script to be able to access my Google sheet I have shared my sheet (used the default setting 'Can edit') with the email address contained in the json file.

Then the Python code to access the Google sheets could look like this:

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

gc = pygsheets.authorize(service_file='service_creds.json')
sh = gc.open('Export Data')
wks_export = sh.worksheet(property='title', value='Export by Month')

# Google worksheet as Pandas dataframe.
export_df = wks_export.get_as_df()

# Here can be done some local operations on the dataframe.

# Updating the worksheet with the values from the modified dataframe.
wks_export.set_dataframe(export_df, start='A1', fit=True)
</code>```

Body:
I'm trying to read from a personal spreadsheet using the google api (v4) for spreadsheets.

I copied the code from the example google is providing while changing the spreadsheet id, range name and scope.

No matter what I do (make the spreadsheet public etc) I'm getting an HttpError: 404 Requested entity was not found.

My Code:

import httplib2
import os

from apiclient import discovery
from oauth2client import client
from oauth2client import tools
from oauth2client.file import Storage

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly',
        'https://www.googleapis.com/auth/spreadsheets',
        'https://www.googleapis.com/auth/drive',
        'https://www.googleapis.com/auth/drive.readonly']
CLIENT_SECRET_FILE = 'client_secret.json'
APPLICATION_NAME = 'python'

def get_credentials():
    home_dir = os.path.expanduser('~')
    credential_dir = os.path.join(home_dir, '.credentials')
    if not os.path.exists(credential_dir):
        os.makedirs(credential_dir)
    credential_path = os.path.join(credential_dir,
            'sheets.googleapis.com-python.json')

    store = Storage(credential_path)
    credentials = store.get()
    if not credentials or credentials.invalid:
        flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES)
        flow.user_agent = APPLICATION_NAME
        credentials = tools.run_flow(flow, store, None)
    return credentials

def main():
    credentials = get_credentials()
    http = credentials.authorize(httplib2.Http())
    discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?'
            'version=v4')
    service = discovery.build('sheets', 'v4', http = http,
            discoveryServiceUrl = discoveryUrl)
    spreadsheetId = 'ID'
    rangeName = 'RANGE'
    result = service.spreadsheets().values().get(
            spreadsheetId = spreadsheetId, range = rangeName).execute()

Body:
Seeing  quite a lot of boilerplate code in the question and the previous answer for a seemingly simple task I would like to share my recipe for this using  pygsheets.

To be able to access Google Sheets API from Python script, I have registered on Google API Dashboard and have chosen Signed Credentials option described in Authorizing pygsheets.

I have downloaded a json file with credentials from Google API and saved it in the same directory as my Python script. The json file contains a dedicated email address that looks like

x-...updater@x...updater.iam.gserviceaccount.com

For the script to be able to access my Google sheet I have shared my sheet (used the default setting 'Can edit') with the email address contained in the json file.

Then the Python code to access the Google sheets could look like this:

import pygsheets
import pandas as pd

gc = pygsheets.authorize(service_file='service_creds.json')
sh = gc.open('Export Data')
wks_export = sh.worksheet(property='title', value='Export by Month')

# Google worksheet as Pandas dataframe.
export_df = wks_export.get_as_df()

# Here can be done some local operations on the dataframe.

# Updating the worksheet with the values from the modified dataframe.
wks_export.set_dataframe(export_df, start='A1', fit=True)


问:
Replace empty alt tag on  tag
I would like to replace my empty alt tags on images in a string. I have a string that contains all the text for a curtain page. In the text are also images, and a lot of them have empty tags (old data), but most of the time they do have title tags.
For example:

What I wish to have:

So:
I need to find all the images in my string, loop trough the images, find title tags, find alt tags, and replace the empty alt tags with the title tags that do have a value.

This is what i tried:

```<code>preg_match_all('/<img[^>]+>/i',$return, $text);
if(isset($text)) {
            foreach( $text as $itemImg ) {
                foreach( $itemImg as $item ) {
                    $array = array();
                    preg_match( '/title="([^"]*)"/i', $item, $array );
                    if(isset($array[1])) {
                        //So $array[1] is a title tag, now what?
                    }
                }

            }
        }
</code>```

I don't know have to complete the code, and I think there must be a easier fix for this. Suggestions?

答:
What you want here is an HTML parser library that can manipulate HTML and then save it again. By using regular expressions to modify HTML markup, you're setting yourself up for a mess.

The DOM module built into PHP offers this functionality: http://php.net/manual/en/book.dom.php

Here's an example (cribbed from this article):

```<code>$dom = new DOMDocument;
$dom->loadHTML($html);
$images = $dom->getElementsByTagName('img');
foreach ($images as $image) {
        $image->setAttribute('src', 'http://example.com/' . $image->getAttribute('src'));
}
$html = $dom->saveHTML();
</code>```

Title: Replace empty alt tag on <img> tag

Body:
I would like to replace my empty alt tags on images in a string. I have a string that contains all the text for a curtain page. In the text are also images, and a lot of them have empty tags (old data), but most of the time they do have title tags.
For example:

What I wish to have:

So:
I need to find all the images in my string, loop trough the images, find title tags, find alt tags, and replace the empty alt tags with the title tags that do have a value.

This is what i tried:

preg_match_all('/]+>/i',$return, $text);
if(isset($text)) {
            foreach( $text as $itemImg ) {
                foreach( $itemImg as $item ) {
                    $array = array();
                    preg_match( '/title="([^"]*)"/i', $item, $array );
                    if(isset($array[1])) {
                        //So $array[1] is a title tag, now what?
                    }
                }

            }
        }

I don't know have to complete the code, and I think there must be a easier fix for this. Suggestions?

Body:
What you want here is an HTML parser library that can manipulate HTML and then save it again. By using regular expressions to modify HTML markup, you're setting yourself up for a mess.

The DOM module built into PHP offers this functionality: http://php.net/manual/en/book.dom.php

Here's an example (cribbed from this article):

$dom = new DOMDocument;
$dom->loadHTML($html);
$images = $dom->getElementsByTagName('img');
foreach ($images as $image) {
        $image->setAttribute('src', 'http://example.com/' . $image->getAttribute('src'));
}
$html = $dom->saveHTML();


问:
Can I Dynamically Update HTML Over a Web Socket?
I'm new to web sockets, and I'm building a web GUI around a command line interface that talks to IoT devices. Some of the commands take several seconds to run because I'm sending them to a large network of devices.

It would be very helpful if I could pass the individual device responses to a textarea (resembling a terminal window) as the responses come in.

I'm using a web socket connection between the client and server. Here is a simplified example of the effect I'm trying to achieve.

```<code>#!/usr/bin/env perl

use strict;
use warnings;
use Mojolicious::Lite;

get '/' => 'index';

websocket '/get_loop' => sub {

    my $c = shift;

    $c->app->log->debug("Websocket opened");

    $c->on(message => sub {

        my ($c, $msg) = @_;

        for (my $i = 0; $i < 10; $i++) {
            $c->send("$i\n");
            $c->app->log->debug("$i\n");
            sleep (1);
        };
    });

    $c->on(finish => sub {
        my ($c, $code, $reason) = @_;
        $c->app->log->debug("WebSocket closed with status $code");
    });
};

app->start;

__DATA__

@@ index.html.ep
<html>
<head>
    <title>Websocket Test</title>
    <style type="text/css">
        textarea {
            height: 200px;
            width: 100px;
        }
    </style>
</head>
<body>
    <h1>Websocket Test</h1>
    <textarea id="terminal" readonly></textarea>
    <script type="text/javascript">
        var ws = new WebSocket('ws://127.0.0.1:3000/get_loop');
        var terminal = document.getElementById('terminal');
        ws.onopen = function() {
            ws.send('start');
        };
        ws.onmessage = function(event) {
            var msg = event.data;
            terminal.innerHTML += msg;
        };
    </script>
</body>
</html>
</code>```

In this example, I have a loop counting from 0 to 14. I'm trying to send each count to the textarea via the web socket for display as the loop counts up. The loop simulates the CLI responses in my actual application. Is this even possible to do over a web socket?

-EDIT-

Modifying the above web socket code and using Mojo::IOLoop->recurring solves the problem without having to resort to multithreading.

```<code>websocket '/get_loop' => sub {

    my $c = shift;

    $c->app->log->debug("Websocket opened");

    my $id;
    $c->on(message => sub {

        my ($c, $msg) = @_;

        my $count = 0;
        $id = Mojo::IOLoop->recurring(1 => sub {
            $c->send("$count\n");
            $c->app->log->debug($count);
            sleep (1);
            $c->finish if $count++ == 10;
        });
    });

    $c->on(finish => sub {
        my ($c, $code, $reason) = @_;
        Mojo::IOLoop->remove($id);
        $c->app->log->debug("WebSocket closed with status $code");
    });
};
</code>```

答:
you need to make javascript websocket listener on the page and insert the data passed into the DOM.

```<code>const socket = new WebSocket('ws://localhost:8080');
socket.addEventListener('open', function (event) {
    socket.send('Hello Server!');
});

socket.addEventListener('message', function (event) {
    var div = document.getElementById('divID');
    div.innerHTML = event.data;
});
</code>```

oh I see, your question is about the server Perl code. What I suggest is making a static array of connections in in the Perl, assigning them on open event, deassigning them on close event. You shouldn't need to send the data back in the on message sub. You could use Event::Lib $event->add( [$timeout] )  and then enumerate over the open sockets (to various different web clients) calling the $c->send("$i\n"); it's totally asynchronous, so no need to tie this to an on message sub. The server can do it whenever it wants.

Make static variable in PERL:
http://www.perlmonks.org/?node_id=8299

Body:
I'm new to web sockets, and I'm building a web GUI around a command line interface that talks to IoT devices. Some of the commands take several seconds to run because I'm sending them to a large network of devices.

It would be very helpful if I could pass the individual device responses to a textarea (resembling a terminal window) as the responses come in.

I'm using a web socket connection between the client and server. Here is a simplified example of the effect I'm trying to achieve.

#!/usr/bin/env perl

use strict;
use warnings;
use Mojolicious::Lite;

get '/' => 'index';

websocket '/get_loop' => sub {

    my $c = shift;

    $c->app->log->debug("Websocket opened");

    $c->on(message => sub {

        my ($c, $msg) = @_;

        for (my $i = 0; $i < 10; $i++) {
            $c->send("$i\n");
            $c->app->log->debug("$i\n");
            sleep (1);
        };
    });

    $c->on(finish => sub {
        my ($c, $code, $reason) = @_;
        $c->app->log->debug("WebSocket closed with status $code");
    });
};

app->start;

__DATA__

@@ index.html.ep

    <title>Websocket Test</title>

Websocket Test

    <textarea id="terminal" readonly></textarea>

In this example, I have a loop counting from 0 to 14. I'm trying to send each count to the textarea via the web socket for display as the loop counts up. The loop simulates the CLI responses in my actual application. Is this even possible to do over a web socket?

-EDIT-

Modifying the above web socket code and using Mojo::IOLoop->recurring solves the problem without having to resort to multithreading.

websocket '/get_loop' => sub {

    my $c = shift;

    $c->app->log->debug("Websocket opened");

    my $id;
    $c->on(message => sub {

        my ($c, $msg) = @_;

        my $count = 0;
        $id = Mojo::IOLoop->recurring(1 => sub {
            $c->send("$count\n");
            $c->app->log->debug($count);
            sleep (1);
            $c->finish if $count++ == 10;
        });
    });

    $c->on(finish => sub {
        my ($c, $code, $reason) = @_;
        Mojo::IOLoop->remove($id);
        $c->app->log->debug("WebSocket closed with status $code");
    });
};

Body:
you need to make javascript websocket listener on the page and insert the data passed into the DOM.

const socket = new WebSocket('ws://localhost:8080');
socket.addEventListener('open', function (event) {
    socket.send('Hello Server!');
});

socket.addEventListener('message', function (event) {
    var div = document.getElementById('divID');
    div.innerHTML = event.data;
});

oh I see, your question is about the server Perl code. What I suggest is making a static array of connections in in the Perl, assigning them on open event, deassigning them on close event. You shouldn't need to send the data back in the on message sub. You could use Event::Lib $event->add( [$timeout] )  and then enumerate over the open sockets (to various different web clients) calling the $c->send("$i\n"); it's totally asynchronous, so no need to tie this to an on message sub. The server can do it whenever it wants.

Make static variable in PERL:
http://www.perlmonks.org/?node_id=8299


问:
How to know if error from dotnet build command with powershell
I want custom script when my dotnet build command failed.
How to get errors list or not success flag when the 'dotnet build' failed with errors compilation for example, or when 'dotnet test' failed, with powershell.

I want equivalent code

```<code>dotnet test
if (!$result.Successful) {
    $errorMessage = ""
    if ($result.Error -ne $null) {
        $errorMessage = $result.Error.Message
    }
    Write-Host "##vso[task.logissue type=error;] error"
    Write-Host "##vso[task.complete result=Failed;]"
    Exit -1
}
</code>```

答:
For me it worked by just using exec in PS.</p>

```<code>exec { dotnet build $projectFile -o $targetDir -c Release }
</code>```

Body:
I want custom script when my dotnet build command failed.
How to get errors list or not success flag when the 'dotnet build' failed with errors compilation for example, or when 'dotnet test' failed, with powershell.

I want equivalent code

dotnet test
if (!$result.Successful) {
    $errorMessage = ""
    if ($result.Error -ne $null) {
        $errorMessage = $result.Error.Message
    }
    Write-Host "##vso[task.logissue type=error;] error"
    Write-Host "##vso[task.complete result=Failed;]"
    Exit -1
}

Body:
For me it worked by just using exec in PS.

exec { dotnet build $projectFile -o $targetDir -c Release }


问:
Error in deleting a dynamic object?
I am allocating a memory for union in heap and i need to delete the object of union when the element Id of union is 900.

Please help me to delete groupUnion[i] object when the Id is 900
below is my code.

```<code>groupUnion = (SettingsUnion *) malloc(sizeof(SettingsUnion) * (NumAttrs + 1));
if(groupUnion == (SettingsUnion *) NULL)
{
    return (FALSE);
}

for (unsigned int i=0; i < NumAttrs; i++)
{
    inFile.read(reinterpret_cast<char*>(&groupUnion[i]),sizeof(SettingsUnion));
    if(groupUnion[i].Id == 900)
    {
        free groupUnion[i]; // this is bad how can i delete groupUnion[i] when groupUnion[i].Id == 900
        groupUnion[i] = NULL;
    }
}

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

Thanks in Advance!!

答:
Your code fragment free groupUnion[i];groupUnion[i] = NULL lets me assume that you actually want to express an array of pointers to SettingUnion-objects rather than an array of SettingUnion-objects.
So your code could look like as follows (I used your malloc/free-style, though in C++ you actually would use new/delete):

```<code>groupUnion = (SettingsUnion **) malloc(sizeof(SettingsUnion*) * (NumAttrs + 1));
if(groupUnion == NULL)
{
    return (FALSE);
}

for (unsigned int i=0; i < NumAttrs; i++)
{
    groupUnion[i] = malloc(sizeof(SettingsUnion));
    inFile.read(reinterpret_cast<char*>(&groupUnion[i]),sizeof(SettingsUnion));
    if(groupUnion[i]->Id == 900)
    {
        free groupUnion[i];
        groupUnion[i] = NULL;
    }
}
inFile.close()
</code>```

Title: Error in deleting a dynamic object?

Body:
I am allocating a memory for union in heap and i need to delete the object of union when the element Id of union is 900.

Please help me to delete groupUnion[i] object when the Id is 900
below is my code.

groupUnion = (SettingsUnion *) malloc(sizeof(SettingsUnion) * (NumAttrs + 1));
if(groupUnion == (SettingsUnion *) NULL)
{
    return (FALSE);
}

for (unsigned int i=0; i < NumAttrs; i++)
{
    inFile.read(reinterpret_cast<char*>(&groupUnion[i]),sizeof(SettingsUnion));
    if(groupUnion[i].Id == 900)
    {
        free groupUnion[i]; // this is bad how can i delete groupUnion[i] when groupUnion[i].Id == 900
        groupUnion[i] = NULL;
    }
}

inFile.close()

Thanks in Advance!!

Body:
Your code fragment free groupUnion[i];groupUnion[i] = NULL lets me assume that you actually want to express an array of pointers to SettingUnion-objects rather than an array of SettingUnion-objects.
So your code could look like as follows (I used your malloc/free-style, though in C++ you actually would use new/delete):

groupUnion = (SettingsUnion **) malloc(sizeof(SettingsUnion*) * (NumAttrs + 1));
if(groupUnion == NULL)
{
    return (FALSE);
}

for (unsigned int i=0; i < NumAttrs; i++)
{
    groupUnion[i] = malloc(sizeof(SettingsUnion));
    inFile.read(reinterpret_cast<char*>(&groupUnion[i]),sizeof(SettingsUnion));
    if(groupUnion[i]->Id == 900)
    {
        free groupUnion[i];
        groupUnion[i] = NULL;
    }
}
inFile.close()


问:
How do I get binary data from pdf created in libharu?
I have a basic C function, which creates a pdf and then writes it to the disc with HPDF_SaveToFile(pdf, filename) where pdf is an instance of HPDF_Doc.

But now I want to change the function behaviour so instead of writing to disc, it would return pdf as binary, which I will work with elsewhere.

How do I get binary from HPDF_Doc variable?

答:
Solved, here's how I did it:

```<code>HPDF_SaveToStream(pdf);
unsigned int streamSize = HPDF_GetStreamSize(pdf);
unsigned char* buffer = malloc(streamSize * sizeof(buffer));
HPDF_Stream_Read(pdf->stream, buffer, &streamSize);
</code>```

this way buffer variable will contain the binary

Body:
Solved, here's how I did it:

HPDF_SaveToStream(pdf);
unsigned int streamSize = HPDF_GetStreamSize(pdf);
unsigned char* buffer = malloc(streamSize * sizeof(buffer));
HPDF_Stream_Read(pdf->stream, buffer, &streamSize);

this way buffer variable will contain the binary


问:
java.io.IOException on reading class with arabic name
after upgrade to Android Studio2.2preview and gradle plugin 2.2alpha2, I start getting below error with cucumber-java as one of the dependencies

```<code>    Caused by: org.gradle.api.UncheckedIOException: java.io.IOException: Failed to read zip file '/mypath/app/build/intermediates/transforms/proguard/androidTest/dev/jars/3/1f/main.jar'.
        at org.gradle.internal.UncheckedException.throwAsUncheckedException(UncheckedException.java:43)
        at org.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:78)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$IncrementalTaskAction.doExecute(AnnotationProcessingTaskFactory.java:244)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$StandardTaskAction.execute(AnnotationProcessingTaskFactory.java:220)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$IncrementalTaskAction.execute(AnnotationProcessingTaskFactory.java:231)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$StandardTaskAction.execute(AnnotationProcessingTaskFactory.java:209)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeAction(ExecuteActionsTaskExecuter.java:80)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeActions(ExecuteActionsTaskExecuter.java:61)
        ... 60 more
Caused by: java.io.IOException: Failed to read zip file '/mypath/app/build/intermediates/transforms/proguard/androidTest/dev/jars/3/1f/main.jar'.
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:404)
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:348)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZip(IncrementalRelativeFileSets.java:105)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZip(IncrementalRelativeFileSets.java:84)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZipsAndDirectories(IncrementalRelativeFileSets.java:258)
        at com.android.build.gradle.tasks.PackageAndroidArtifact.doFullTaskAction(PackageAndroidArtifact.java:291)
        at com.android.build.gradle.tasks.PackageApplication.doFullTaskAction(PackageApplication.java:73)
        at com.android.build.gradle.internal.tasks.IncrementalTask.taskAction(IncrementalTask.java:88)
        at org.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:75)
        ... 66 more
Caused by: java.io.IOException: Failed to read directory entry index 5072 (total directory bytes read: 558556).
        at com.android.builder.internal.packaging.zip.CentralDirectory.makeFromData(CentralDirectory.java:233)
        at com.android.builder.internal.packaging.zip.ZFile.readCentralDirectory(ZFile.java:650)
        at com.android.builder.internal.packaging.zip.ZFile.readData(ZFile.java:472)
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:399)
        ... 74 more
Caused by: java.io.IOException: Failed to read stored entry 'cucumber/api/java/ar/اذاً.class'.
        at com.android.builder.internal.packaging.zip.CentralDirectory.readEntry(CentralDirectory.java:359)
        at com.android.builder.internal.packaging.zip.CentralDirectory.makeFromData(CentralDirectory.java:230)
        ... 77 more
Caused by: java.io.IOException: Failed to read data descriptor record.
        at com.android.builder.internal.packaging.zip.StoredEntry.<init>(StoredEntry.java:274)
        at com.android.builder.internal.packaging.zip.CentralDirectory.readEntry(CentralDirectory.java:357)
        ... 78 more
Caused by: java.io.IOException: Incorrect value for field 'CRC32': value is 24904624 but 310353179 expected.
        at com.android.builder.internal.packaging.zip.ZipField.verify(ZipField.java:209)
        at com.android.builder.internal.packaging.zip.StoredEntry.readDataDescriptorRecord(StoredEntry.java:475)
        at com.android.builder.internal.packaging.zip.StoredEntry.<init>(StoredEntry.java:272)
        ... 79 more
</code>```

Looks like it's not reading arabic, I have also set below

```<code>compileOptions {
    encoding "UTF-8"
    sourceCompatibility JavaVersion.VERSION_1_7
    targetCompatibility JavaVersion.VERSION_1_7
}
</code>```

but still no luck

答:
According to niqueco answer:
in project level build.gradle use gradle plugin version 2.2.3.

```<code>buildscript {
repositories {
    jcenter()
}
dependencies {
    classpath 'com.android.tools.build:gradle:2.2.3'
}
</code>```

}

Restart Android studio and it should works

Body:
after upgrade to Android Studio2.2preview and gradle plugin 2.2alpha2, I start getting below error with cucumber-java as one of the dependencies

    Caused by: org.gradle.api.UncheckedIOException: java.io.IOException: Failed to read zip file '/mypath/app/build/intermediates/transforms/proguard/androidTest/dev/jars/3/1f/main.jar'.
        at org.gradle.internal.UncheckedException.throwAsUncheckedException(UncheckedException.java:43)
        at org.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:78)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$IncrementalTaskAction.doExecute(AnnotationProcessingTaskFactory.java:244)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$StandardTaskAction.execute(AnnotationProcessingTaskFactory.java:220)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$IncrementalTaskAction.execute(AnnotationProcessingTaskFactory.java:231)
        at org.gradle.api.internal.project.taskfactory.AnnotationProcessingTaskFactory$StandardTaskAction.execute(AnnotationProcessingTaskFactory.java:209)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeAction(ExecuteActionsTaskExecuter.java:80)
        at org.gradle.api.internal.tasks.execution.ExecuteActionsTaskExecuter.executeActions(ExecuteActionsTaskExecuter.java:61)
        ... 60 more
Caused by: java.io.IOException: Failed to read zip file '/mypath/app/build/intermediates/transforms/proguard/androidTest/dev/jars/3/1f/main.jar'.
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:404)
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:348)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZip(IncrementalRelativeFileSets.java:105)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZip(IncrementalRelativeFileSets.java:84)
        at com.android.builder.files.IncrementalRelativeFileSets.fromZipsAndDirectories(IncrementalRelativeFileSets.java:258)
        at com.android.build.gradle.tasks.PackageAndroidArtifact.doFullTaskAction(PackageAndroidArtifact.java:291)
        at com.android.build.gradle.tasks.PackageApplication.doFullTaskAction(PackageApplication.java:73)
        at com.android.build.gradle.internal.tasks.IncrementalTask.taskAction(IncrementalTask.java:88)
        at org.gradle.internal.reflect.JavaMethod.invoke(JavaMethod.java:75)
        ... 66 more
Caused by: java.io.IOException: Failed to read directory entry index 5072 (total directory bytes read: 558556).
        at com.android.builder.internal.packaging.zip.CentralDirectory.makeFromData(CentralDirectory.java:233)
        at com.android.builder.internal.packaging.zip.ZFile.readCentralDirectory(ZFile.java:650)
        at com.android.builder.internal.packaging.zip.ZFile.readData(ZFile.java:472)
        at com.android.builder.internal.packaging.zip.ZFile.<init>(ZFile.java:399)
        ... 74 more
Caused by: java.io.IOException: Failed to read stored entry 'cucumber/api/java/ar/اذاً.class'.
        at com.android.builder.internal.packaging.zip.CentralDirectory.readEntry(CentralDirectory.java:359)
        at com.android.builder.internal.packaging.zip.CentralDirectory.makeFromData(CentralDirectory.java:230)
        ... 77 more
Caused by: java.io.IOException: Failed to read data descriptor record.
        at com.android.builder.internal.packaging.zip.StoredEntry.<init>(StoredEntry.java:274)
        at com.android.builder.internal.packaging.zip.CentralDirectory.readEntry(CentralDirectory.java:357)
        ... 78 more
Caused by: java.io.IOException: Incorrect value for field 'CRC32': value is 24904624 but 310353179 expected.
        at com.android.builder.internal.packaging.zip.ZipField.verify(ZipField.java:209)
        at com.android.builder.internal.packaging.zip.StoredEntry.readDataDescriptorRecord(StoredEntry.java:475)
        at com.android.builder.internal.packaging.zip.StoredEntry.<init>(StoredEntry.java:272)
        ... 79 more

Looks like it's not reading arabic, I have also set below

compileOptions {
    encoding "UTF-8"
    sourceCompatibility JavaVersion.VERSION_1_7
    targetCompatibility JavaVersion.VERSION_1_7
}

but still no luck

Body:
According to niqueco answer:
in project level build.gradle use gradle plugin version 2.2.3.

buildscript {
repositories {
    jcenter()
}
dependencies {
    classpath 'com.android.tools.build:gradle:2.2.3'
}

}

Restart Android studio and it should works


问:
select part of url string in MySQL
If have the following string with a unique element in it. This unique element I need to extract from this string and save it as an variable. The part that I need to extract is: Proto-1088.

```<code>"{"account":"123456","n":{"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}
</code>```

I have tried to use the TRIM function in MySQL but this didn't work since the number in Proto changes.

答:
If that's the only URL in the string you could use SUBSTRING_INDEX

```<code>SELECT
SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  '"{"account":"123456","n":
  {"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}',
  '/', -1),
'"',1)
</code>```

Explanation:

SUBSTRING_INDEX(str, delim, count) finds the index of  count occurrence of delim (counting from left if count is positive, from right if negative) and returns everything past that index (going left if count is positive, right if negative).

So I used SUBSTRING_INDEX with count=-1 to extract everything to the right of the last occurrence of '/' and then took everything to the right of the first occurrence of '" using count=1.

Title: select part of url string in MySQL

Body:
If have the following string with a unique element in it. This unique element I need to extract from this string and save it as an variable. The part that I need to extract is: Proto-1088.

"{"account":"123456","n":{"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}

I have tried to use the TRIM function in MySQL but this didn't work since the number in Proto changes.

Body:
If that's the only URL in the string you could use SUBSTRING_INDEX

SELECT
SUBSTRING_INDEX(
  SUBSTRING_INDEX(
  '"{"account":"123456","n":
  {"Score":"Bad","Ticket":"https://test.test2.com/browse/Proto-1088"},"2":{"Score":"GOOD"}}',
  '/', -1),
'"',1)

Explanation:

SUBSTRING_INDEX(str, delim, count) finds the index of  count occurrence of delim (counting from left if count is positive, from right if negative) and returns everything past that index (going left if count is positive, right if negative).

So I used SUBSTRING_INDEX with count=-1 to extract everything to the right of the last occurrence of '/' and then took everything to the right of the first occurrence of '" using count=1.


问:
Use calculated value for comparison in aggregation
```<code>var data_form = {
  {
    _id : "123",
    result:{
      run:10
    },
    result_re:{
      run:10
    },
    result_ch:{
      run:10
    },
    result_qm:{
      run:10
    }
  },
  {
    _id : "345",
    result:{
      run:20
    },
    result_re:{
      run:20
    },
    result_ch:{
      run:20
    },
    result_qm:{
      run:20
    }
  },
  {
    _id : "567",
    result:{
      run:30
    },
    result_re:{
      run:30
    },
    result_ch:{
      run:30
    },
    result_qm:{
      run:30
    }
  }
}

var pipeline = [
      { $project: {

          total: { $add: [ "$result.run", "$result_re.run", "$result_ch.run", "$result_qm.run"] } ,
          discount:{
                      $cond: [ { $gt: [ total , 50 ] }, 1, 0]
                   }
          }
      },
      { $sort: {total: -1}},
      { $limit : 10 }
]

db.getCollection('game_users').aggregate(pipeline)
</code>```

I need to compare total output with aggregation condition and counter increase if condition match.

My collection is defined in data_form variable.

total field output get from query and if that total is grater than 50 after that counter increase.

答:
You need to specify the expression within the $cond. You cannot reference the value of another calculated field within the same pipeline stage. Either do it twice or put in separate stages. The same stage is the most efficient:

var pipeline = [
  { $project: {
     total: {
         $add: [
           "$result.run",
          "$result_re.run",
          "$result_ch.run",
          "$result_qm.run"
         ]
     } ,
     discount:{
       $cond: [
         { $gt: [
           { $add: [
             "$result.run",
             "$result_re.run",
             "$result_ch.run",
             "$result_qm.run"
           ]},
           50
         ]},
         1,
         0
       ]
     }
  }},
  { $sort: {total: -1}},
  { $limit : 10 }
]
```

<p>Or separate the <code>$project</code> in two stages</p>

<pre class="lang-js prettyprint-override"><code>var pipeline = [
  { $project: {
     total: {
         $add: [
           "$result.run",
          "$result_re.run",
          "$result_ch.run",
          "$result_qm.run"
         ]
     }
  }},
  { $project: {
     total: 1,
     discount:{
       $cond: [
         { $gt: [ "$total", 50 ] }
         1,
         0
       ]
     }
  }},

  }}
  { $sort: {total: -1}},
  { $limit : 10 }
]
</code>```

This looks "prettier" but running another stage means another pass through data, so it's probably best to do in one stage.

To get the "totals" across the collection, run a separate aggregation to the paged results.

var pipeline = [
  { $group: {
     _id: null,
     total: {
       $sum: {
         $add: [
           "$result.run",
           "$result_re.run",
           "$result_ch.run",
           "$result_qm.run"
         ]
       }
     } ,
     discount:{
       $sum: {
         $cond: [
           { $gt: [
             { $add: [
               "$result.run",
               "$result_re.run",
               "$result_ch.run",
               "$result_qm.run"
             ]},
             50
           ]},
           1,
           0
         ]
       }
     }
  }}
];
```

Do not try and get both the paged results and the total in the same response since that is not how you do it. These should be run separately as attempting to return in one result will certainly break the BSON limit in real world use cases.

Body:
var data_form = {
  {
    _id : "123",
    result:{
      run:10
    },
    result_re:{
      run:10
    },
    result_ch:{
      run:10
    },
    result_qm:{
      run:10
    }
  },
  {
    _id : "345",
    result:{
      run:20
    },
    result_re:{
      run:20
    },
    result_ch:{
      run:20
    },
    result_qm:{
      run:20
    }
  },
  {
    _id : "567",
    result:{
      run:30
    },
    result_re:{
      run:30
    },
    result_ch:{
      run:30
    },
    result_qm:{
      run:30
    }
  }
}

var pipeline = [
      { $project: {

          total: { $add: [ "$result.run", "$result_re.run", "$result_ch.run", "$result_qm.run"] } ,
          discount:{
                      $cond: [ { $gt: [ total , 50 ] }, 1, 0]
                   }
          }
      },
      { $sort: {total: -1}},
      { $limit : 10 }
]

db.getCollection('game_users').aggregate(pipeline)

I need to compare total output with aggregation condition and counter increase if condition match.

My collection is defined in data_form variable.

total field output get from query and if that total is grater than 50 after that counter increase.

Body:
You need to specify the expression within the $cond. You cannot reference the value of another calculated field within the same pipeline stage. Either do it twice or put in separate stages. The same stage is the most efficient:

var pipeline = [
  { $project: {
     total: {
         $add: [
           "$result.run",
          "$result_re.run",
          "$result_ch.run",
          "$result_qm.run"
         ]
     } ,
     discount:{
       $cond: [
         { $gt: [
           { $add: [
             "$result.run",
             "$result_re.run",
             "$result_ch.run",
             "$result_qm.run"
           ]},
           50
         ]},
         1,
         0
       ]
     }
  }},
  { $sort: {total: -1}},
  { $limit : 10 }
]

Or separate the $project in two stages

var pipeline = [
  { $project: {
     total: {
         $add: [
           "$result.run",
          "$result_re.run",
          "$result_ch.run",
          "$result_qm.run"
         ]
     }
  }},
  { $project: {
     total: 1,
     discount:{
       $cond: [
         { $gt: [ "$total", 50 ] }
         1,
         0
       ]
     }
  }},

  }}
  { $sort: {total: -1}},
  { $limit : 10 }
]

This looks "prettier" but running another stage means another pass through data, so it's probably best to do in one stage.

To get the "totals" across the collection, run a separate aggregation to the paged results.

var pipeline = [
  { $group: {
     _id: null,
     total: {
       $sum: {
         $add: [
           "$result.run",
           "$result_re.run",
           "$result_ch.run",
           "$result_qm.run"
         ]
       }
     } ,
     discount:{
       $sum: {
         $cond: [
           { $gt: [
             { $add: [
               "$result.run",
               "$result_re.run",
               "$result_ch.run",
               "$result_qm.run"
             ]},
             50
           ]},
           1,
           0
         ]
       }
     }
  }}
];

Do not try and get both the paged results and the total in the same response since that is not how you do it. These should be run separately as attempting to return in one result will certainly break the BSON limit in real world use cases.


问:
Static allocation of opaque data types
Very often malloc() is absolutely not allowed when programming for embedded systems. Most of the time I'm pretty able to deal with this, but one thing irritates me: it keeps me from using so called 'opaque types' to enable data hiding. Normally I'd do something like this:

```<code>// In file module.h
typedef struct handle_t handle_t;

handle_t *create_handle();
void operation_on_handle(handle_t *handle, int an_argument);
void another_operation_on_handle(handle_t *handle, char etcetera);
void close_handle(handle_t *handle);

// In file module.c
struct handle_t {
    int foo;
    void *something;
    int another_implementation_detail;
};

handle_t *create_handle() {
    handle_t *handle = malloc(sizeof(struct handle_t));
    // other initialization
    return handle;
}
</code>```

There you go: create_handle() performs a malloc() to create an 'instance'. A construction often used to prevent having to malloc() is to change the prototype of create_handle() like this:

```<code>void create_handle(handle_t *handle);
</code>```

And then the caller could create the handle this way:

```<code>// In file caller.c
void i_am_the_caller() {
    handle_t a_handle;    // Allocate a handle on the stack instead of malloc()
    create_handle(&a_handle);
    // ... a_handle is ready to go!
}
</code>```

But unfortunately this code is obviously invalid, the size of handle_t isn't known!

I never really found a solution to solve this in a proper way. I'd very like to know if anyone has a proper way of doing this, or maybe a complete different approach to enable data hiding in C (not using static globals in the module.c of course, one must be able to create multiple instances).

答:
Unfortunately, I think the typical way to deal with this problem is by simply having the programmer treat the object as opaque - the full structure implementation is in the header and available, it's just the responsibility of the programmer to not use the internals directly, only through the APIs defined for the object.

If this isn't good enough, a few options might be:

use C++ as a 'better C' and declare the internals of the structure as private.

run some sort of pre-processor on the headers so that the internals of the structure are declared, but with unusable names.  The original header, with good names, will be available to the implementation of the APIs that manage the structure. I've never seen this technique used - it's just an idea off the top of my head that might be possible, but seems like far more trouble than it's worth.

have your code that uses opaque pointers declare the statically allocated objects as extern (ie., globals) Then have a special module that has access to the full definition of the object actually declare these objects.  Since only the 'special' module has access to the full definition, the normal use of the opaque object remains opaque. However, now you have to rely on your programmers to not abuse the fact that thee objects are global.  You have also increased the change of naming collisions, so that need to be managed (probably not a big problem, except that it might occur unintentionally - ouch!).

I think overall, just relying on your programmers to follow the rules for the use of these objects might be the best solution (though using a subset of C++ isn't bad either in my opinion).  Depending on your programmers to follow the rules of not using the structure internals isn't perfect, but it's a workable solution that is in common use.

ContentLicense: CC BY-SA 2.5

Title: Static allocation of opaque data types

Body:
Very often malloc() is absolutely not allowed when programming for embedded systems. Most of the time I'm pretty able to deal with this, but one thing irritates me: it keeps me from using so called 'opaque types' to enable data hiding. Normally I'd do something like this:

// In file module.h
typedef struct handle_t handle_t;

handle_t *create_handle();
void operation_on_handle(handle_t *handle, int an_argument);
void another_operation_on_handle(handle_t *handle, char etcetera);
void close_handle(handle_t *handle);

// In file module.c
struct handle_t {
    int foo;
    void *something;
    int another_implementation_detail;
};

handle_t *create_handle() {
    handle_t *handle = malloc(sizeof(struct handle_t));
    // other initialization
    return handle;
}

There you go: create_handle() performs a malloc() to create an 'instance'. A construction often used to prevent having to malloc() is to change the prototype of create_handle() like this:

void create_handle(handle_t *handle);

And then the caller could create the handle this way:

// In file caller.c
void i_am_the_caller() {
    handle_t a_handle;    // Allocate a handle on the stack instead of malloc()
    create_handle(&a_handle);
    // ... a_handle is ready to go!
}

But unfortunately this code is obviously invalid, the size of handle_t isn't known!

I never really found a solution to solve this in a proper way. I'd very like to know if anyone has a proper way of doing this, or maybe a complete different approach to enable data hiding in C (not using static globals in the module.c of course, one must be able to create multiple instances).


问:
An empty lock to guarantee thread completion
My c# service looks like this:

```<code>public partial class MyService : ServiceBase
{
    private object @lock = new object();
    private CancellationTokenSource cancellationSource = new CancellationTokenSource();
    private Timer timer = new Timer(Worker, null, 0, 20*1000);

    private void Worker(Object stateInfo)
    {
        lock (@lock) {
            if (cancellationSource.IsCancellationRequested)
                return;

            //code that must not execute after OnStop

        }
    }

    protected override void OnStop()
    {
        cancellationSource.Cancel();

        lock (@lock) {}

        //Worker is guaranteed not to execute any more code here
    }

}
</code>```

As you can see, Worker should not run any more code after OnStop, and I think the empty lock sorts that out, since even if the Worker enters the lock after it, it will immediately exit, due to the cancellation token.

However I have never seen an empty lock, so I'm concerned I missed something. Did I?

答:
The lock serves no goal here. What you are trying to achieve can be done better by using Task:

```<code>Task.Run(Worker, cancellationToken);
</code>```

If the timer is hit, the cancellation token will immediate stop the task from running further.

Body:
My c# service looks like this:

public partial class MyService : ServiceBase
{
    private object @lock = new object();
    private CancellationTokenSource cancellationSource = new CancellationTokenSource();
    private Timer timer = new Timer(Worker, null, 0, 20*1000);

    private void Worker(Object stateInfo)
    {
        lock (@lock) {
            if (cancellationSource.IsCancellationRequested)
                return;

            //code that must not execute after OnStop

        }
    }

    protected override void OnStop()
    {
        cancellationSource.Cancel();

        lock (@lock) {}

        //Worker is guaranteed not to execute any more code here
    }

}

As you can see, Worker should not run any more code after OnStop, and I think the empty lock sorts that out, since even if the Worker enters the lock after it, it will immediately exit, due to the cancellation token.

However I have never seen an empty lock, so I'm concerned I missed something. Did I?

Body:
The lock serves no goal here. What you are trying to achieve can be done better by using Task:

Task.Run(Worker, cancellationToken);

If the timer is hit, the cancellation token will immediate stop the task from running further.


问:
Comparing tables in MySQL and adding a yes/no response
Can someone help me tweak my SQL to get the desired result. I have a table of users, a table of cases, and a relationship table for users attached to cases.

Users Table - users

```<code>+----+-------+--------+
| id | first |  last  |
+----+-------+--------+
|  1 | Joe   | Bloggs |
|  2 | John  | Doe    |
|  3 | Jane  | Doe    |
|  4 | Dave  | Smith  |
+----+-------+--------+
</code>```

Case Table - cases

```<code>+----+--------+------+
| id |  Case  | Code |
+----+--------+------+
|  1 | Case 1 | C1   |
|  2 | Case 2 | C2   |
|  3 | Case 3 | C3   |
+----+--------+------+
</code>```

Case Users Table - case_users

```<code>+----+---------+---------+
| id | case_id | user_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       2 |       4 |
|  4 |       3 |       1 |
|  5 |       1 |       4 |
+----+---------+---------+
</code>```

I want to query the database to return me a full list of users and return a yes or no if they are linked to case 1 and then case 2 case 3 etc.

The SQL I've got so far (selecting for Case ID 1) returns all users but is assigning 'no' to everyone:

```<code>SELECT
    users.first,
    users.last,
    CASE WHEN case_users.case_id IS NULL THEN 'N' ELSE 'Y' END AS 'yes/no'
FROM
    users
        LEFT OUTER JOIN case_users ON case_users.case_id = 1
</code>```

My actual result is:

```<code>+-------+--------+--------+
| First |  Last  | Yes/No |
+-------+--------+--------+
| Joe   | Bloggs | Y      |
| John  | Doe    | Y      |
| Jane  | Doe    | Y      |
| Dave  | Smith  | Y      |
+-------+--------+--------+
</code>```

My desired result should be:

```<code>+-------+--------+--------+
| First |  Last  | Yes/No |
+-------+--------+--------+
| Joe   | Bloggs | Y      |
| John  | Doe    | Y      |
| Jane  | Doe    | N      |
| Dave  | Smith  | Y      |
+-------+--------+--------+
</code>```

Can someone help me as I can't get the desired result?

答:
You can try this one.

```<code>SELECT u.first, u.last, IF(cu.case_id,'Y','N') AS CASE_1 FROM users u
LEFT JOIN case_users cu ON u.id = cu.user_id AND cu.case_id = 1
</code>```

Can use IF(cu.case_id,'Y','N')condition also instead of CASE

Body:
Can someone help me tweak my SQL to get the desired result. I have a table of users, a table of cases, and a relationship table for users attached to cases.

Users Table - users

+----+-------+--------+
| id | first |  last  |
+----+-------+--------+
|  1 | Joe   | Bloggs |
|  2 | John  | Doe    |
|  3 | Jane  | Doe    |
|  4 | Dave  | Smith  |
+----+-------+--------+

Case Table - cases

+----+--------+------+
| id |  Case  | Code |
+----+--------+------+
|  1 | Case 1 | C1   |
|  2 | Case 2 | C2   |
|  3 | Case 3 | C3   |
+----+--------+------+

Case Users Table - case_users

+----+---------+---------+
| id | case_id | user_id |
+----+---------+---------+
|  1 |       1 |       1 |
|  2 |       1 |       2 |
|  3 |       2 |       4 |
|  4 |       3 |       1 |
|  5 |       1 |       4 |
+----+---------+---------+

I want to query the database to return me a full list of users and return a yes or no if they are linked to case 1 and then case 2 case 3 etc.

The SQL I've got so far (selecting for Case ID 1) returns all users but is assigning 'no' to everyone:

SELECT
    users.first,
    users.last,
    CASE WHEN case_users.case_id IS NULL THEN 'N' ELSE 'Y' END AS 'yes/no'
FROM
    users
        LEFT OUTER JOIN case_users ON case_users.case_id = 1

My actual result is:

+-------+--------+--------+
| First |  Last  | Yes/No |
+-------+--------+--------+
| Joe   | Bloggs | Y      |
| John  | Doe    | Y      |
| Jane  | Doe    | Y      |
| Dave  | Smith  | Y      |
+-------+--------+--------+

My desired result should be:

+-------+--------+--------+
| First |  Last  | Yes/No |
+-------+--------+--------+
| Joe   | Bloggs | Y      |
| John  | Doe    | Y      |
| Jane  | Doe    | N      |
| Dave  | Smith  | Y      |
+-------+--------+--------+

Can someone help me as I can't get the desired result?

Body:
You can try this one.

SELECT u.first, u.last, IF(cu.case_id,'Y','N') AS CASE_1 FROM users u
LEFT JOIN case_users cu ON u.id = cu.user_id AND cu.case_id = 1

Can use IF(cu.case_id,'Y','N')condition also instead of CASE


问:
Using JavaScript to edit HTML style in .cshtml
I am making a MVC Web Application and trying to edit the width of a progress bar to show an increase or decrease in the progress and am using a basic javascript function to do it but it is not working.

Can anyone tell me what am I doing wrong? Thanks.

@{
    ViewBag.Title = "About";
}

@ViewBag.Title.

@ViewBag.Message

Use this area to provide additional information.

        45% Complete

<input type="button" value="Change Width" onclick="increase()" class="btn btn-default btn-sm" />

```

答:
I will suggest you to use jQUery and attach click event like this -

$('#change-width').on('click',function(){
    $('#pb1').css('width','80%');
    $('#pb1').attr('aria-valuenow','80');
    $('.sr-only').text('80% Complete');
});```
<pre class="snippet-code-css lang-css prettyprint-override"><code>#pb1
{
  background-color:#09F;
  color:#FFF;
  font-size:14px;
  padding:4px;
  border-radius:2px;
}</code>```

@{
    ViewBag.Title = "About";
}

@ViewBag.Title.

@ViewBag.Message

Use this area to provide additional information.

            45% Complete

    <input type="button" value="Change Width" id="change-width" class="btn btn-default btn-sm" />```

Make sure you update following data while you change the progress bar-

Width of bar.

aria-valuenow attribute

Percentage text

To get the correct output.

Body:
I will suggest you to use jQUery and attach click event like this -

$('#change-width').on('click',function(){
    $('#pb1').css('width','80%');
    $('#pb1').attr('aria-valuenow','80');
    $('.sr-only').text('80% Complete');
});

#pb1
{
  background-color:#09F;
  color:#FFF;
  font-size:14px;
  padding:4px;
  border-radius:2px;
}

<script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.1/jquery.min.js"></script>
@{
    ViewBag.Title = "About";
}

    <h2>@ViewBag.Title.</h2>
    <h3>@ViewBag.Message</h3>

    <p>Use this area to provide additional information.</p>

    <div class="progress progress-striped active progress-sm">
        <div class="progress-bar progress-bar-success" name="blah" role="progressbar" aria-valuenow="45" aria-valuemin="0" aria-valuemax="100" style="width: 45%"  id="pb1">
            <span class="sr-only">45% Complete</span>
        </div>
    </div>
    <br/>
    <input type="button" value="Change Width" id="change-width" class="btn btn-default btn-sm" />

Make sure you update following data while you change the progress bar-

Width of bar.

aria-valuenow attribute

Percentage text

To get the correct output.


问:
How to check existing environment variable using a var in bash?

Context

I am writting a .sh file which read a .config file. In this .config file (which I can't edit) there are some variables. I want to test if those variables are defined as environment variables.

.config file:

```<code>APPLICATION_PATH=/var/www/application
MONGO_DATA_PATH=/var/lib/mongodb
MYSQL_DATA_PATH=/var/lib/mysql
</code>```

test.sh file:

```<code>#!/bin/sh
if test -e ../my_folder/.config                      # Test if the .config file exists
then
    cat ../my_folder/.config | while read line; do   # Read the .config file line per line

        env_var="${line%=*}"                         # Get the part before the '=' character of the current line (e.G. APPLICATION_PATH)
        echo $env_var                                # Display the var (e.G. APPLICATION_PATH)

                                                     # Here, I would like to display the env var,
                                                     # e.G. like it would do using echo $APPLICATION_PATH
                                                     # but using the $env_var

        ( "echo \$$env_var" )                        # Throw an error
    done
fi
</code>```

Problem

It seems that ( "echo \$$env_var" ) is not possible. When I run test.sh, it displays this:

APPLICATION_PATH

./test.sh: ligne 13: echo $APPLICATION_PATH : not found

MONGO_DATA_PATH

./test.sh: ligne 13: echo $MONGO_DATA_PATH : not found

MYSQL_DATA_PATH

./test.sh: ligne 13: echo $MYSQL_DATA_PATH : not found

Question

How can I test if there is an environment variable using $env_var?

答:
You could use the method mentioned here: Is it possible to build variable names from other variables in bash?

So accordingly your code would change to the following:

```<code>#!/bin/sh
if test -e ../my_folder/.config
then
cat ../my_folder/.config | while read line; do

    env_var="${line%=*}"
    echo $env_var
    eval "echo \$$env_var"                        # This works
done
fi
</code>```

Body:
Context

I am writting a .sh file which read a .config file. In this .config file (which I can't edit) there are some variables. I want to test if those variables are defined as environment variables.

.config file:

APPLICATION_PATH=/var/www/application
MONGO_DATA_PATH=/var/lib/mongodb
MYSQL_DATA_PATH=/var/lib/mysql

test.sh file:

#!/bin/sh
if test -e ../my_folder/.config                      # Test if the .config file exists
then
    cat ../my_folder/.config | while read line; do   # Read the .config file line per line

        env_var="${line%=*}"                         # Get the part before the '=' character of the current line (e.G. APPLICATION_PATH)
        echo $env_var                                # Display the var (e.G. APPLICATION_PATH)

                                                     # Here, I would like to display the env var,
                                                     # e.G. like it would do using echo $APPLICATION_PATH
                                                     # but using the $env_var

        ( "echo \$$env_var" )                        # Throw an error
    done
fi

Problem

It seems that ( "echo \$$env_var" ) is not possible. When I run test.sh, it displays this:

APPLICATION_PATH

./test.sh: ligne 13: echo $APPLICATION_PATH : not found

MONGO_DATA_PATH

./test.sh: ligne 13: echo $MONGO_DATA_PATH : not found

MYSQL_DATA_PATH

./test.sh: ligne 13: echo $MYSQL_DATA_PATH : not found

Question

How can I test if there is an environment variable using $env_var?

Body:
You could use the method mentioned here: Is it possible to build variable names from other variables in bash?

So accordingly your code would change to the following:

#!/bin/sh
if test -e ../my_folder/.config
then
cat ../my_folder/.config | while read line; do

    env_var="${line%=*}"
    echo $env_var
    eval "echo \$$env_var"                        # This works
done
fi