michelp

(setq-default indent-tabs-mode nil)
(add-to-list 'load-path "~/.emacs.d")
(load-library "picolisp.el")

(tool-bar-mode -1)
(menu-bar-mode -1)
(scroll-bar-mode -1)

(require 'color-theme)
(color-theme-initialize)

michelp

politips=# select * from normies where typ = 'PERSON' and position(' ' in id) > 0 order by hits desc limit 45;                                                                                                      
         id          | hits  |  typ   
---------------------+-------+--------
 Donald Trump        | 31325 | PERSON
 Planned Parenthood  | 25630 | PERSON
 Barack Obama        | 21343 | PERSON
 Hillary Clinton     | 19276 | PERSON
 Fox News            | 11867 | PERSON
 Jeb Bush            | 10285 | PERSON
 Pope Francis        |  9935 | PERSON

michelp

create or replace function news.insert_article(text, nilsimsa, jsonb, uuid, boolean) returns uuid as $$
with article as (
   insert into news.article (url, simhash, data, dup) values ($1, $2, $3, $5) returning *
  ), doc_entities as (
   select (select id from article), e.key as entity_id, sum(e.hits) as hits
   from news.entities_from_data((select data from article)->'entities') e group by id, e.key                                                                                                                        
  ), global_entities as (
   insert into news.entities as ne (id, hits) (select entity_id, hits from doc_entities)                                                                                                                           
   on conflict (id) do update set hits = ne.hits + excluded.hits                                                                                                                                                    
  ), article_entities as (

michelp

"""\
pyrtlsdr server

Usage:
  net.py <frequency> [options]
  net.py (-h | --help)
  net.py --version

Options:
  -h --help             Show this screen.

michelp

politips=# select key, regr_slope(hits, extract(epoch from val)) as slope from (select j.key, sum(j.value::int) as hits, date_trunc('day', created) as val from news.article a, jsonb_each_text(a.data->'entities'->'PERSON') j, news.entity_summary e where a.created > now() - '1 week'::interval and j.key = e.key and position(' ' in j.key) > 0 and e.total > 1000 group by j.key, val order by val, j.key) i group by key order by slope desc nulls last;  

        key         |         slope         
--------------------+-----------------------
 Planned Parenthood |  0.000882936507936508
 Donald Trump       |  0.000549768518518519
 Fox News           |  0.000435130070546737
 Barack Obama       |  0.000291694223985891
 Hillary Clinton    |  0.000229828042328042
 Jeb Bush           |   0.00021549823633157

michelp

politips=# create index news_entity_summary_key_idx on news.entity_summary using btree (lower(key) text_pattern_ops);                                          
CREATE INDEX
politips=# cluster news.entity_summary using news_entity_summary_key_idx;
CLUSTER
politips=# explain select * from news.entity_summary where lower(key) like 'bob%' order by total desc;
                                            QUERY PLAN                                             
---------------------------------------------------------------------------------------------------
 Sort  (cost=877.79..879.44 rows=663 width=18)
   Sort Key: total
   ->  Bitmap Heap Scan on entity_summary  (cost=19.22..846.72 rows=663 width=18)

michelp

drop schema if exists util cascade;
create schema util;

create or replace function util.nilsimsa_distance(hash1 text, hash2 text) returns int as $$
declare
    bits int := 0;
    POPC int[] := array[0, 1, 1, 2, 1, 2, 2, 3, 1, 2, 2, 3, 2, 3, 3, 4,
                        1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
                        1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
                        2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,

michelp

create or replace function nilsimsa_distance(hash1 text, hash2 text) returns bigint as $$
declare
    bits bigint := 0;
    POPC bigint[] := array[0, 1, 1, 2, 1, 2, 2, 3, 1, 2, 2, 3, 2, 3, 3, 4,
                           1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
                           1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
                           2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
                           1, 2, 2, 3, 2, 3, 3, 4, 2, 3, 3, 4, 3, 4, 4, 5,
                           2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,
                           2, 3, 3, 4, 3, 4, 4, 5, 3, 4, 4, 5, 4, 5, 5, 6,

michelp

if __name__ == '__main__':
    import atexit
    from multiprocessing import Process
    device = zmq.devices.ProcessDevice(zmq.QUEUE, zmq.ROUTER, zmq.DEALER)
    device.bind_in('ipc://bind_in')
    device.bind_out('ipc://bind_out')
    device.start()

    def hello(name):
        return 'hello %s' % name

michelp

create type search.amounts_by_code as (total integer, purpose_code text);

create or replace function search.amount_by_code(auser text, apath jsonb, args jsonb)
  returns setof search.amounts_by_code as $$

  select (sum(amount), purpose_codes)::search.amounts_by_code
      from working_transactions
      where tran_date > '2012-01-01' and tran_date < '2013-01-01'
      and filer ilike '%' || (apath->>0) || '%'
  group by purpose_codes order by sum(amount) desc;