alastair

  SELECT listen.id
       , user_id
       , extract(epoch from ts)
       , artist_msid
       , album_msid
       , recording_msid
       , data
    FROM listen
    JOIN listen_json USING (id)
   WHERE user_id = :user_Id

alastair

package acousticbrainz;

message Lowlevel {
    optional repeated int32 spectral_complexity = 1;
    optional repeated int32 silence_rate_20dB = 2;
    optional float average_loudness = 3;
    optional repeated float erbbands_spread = 4;
}

alastair

def import_single(connection, data):
  q = "insert into ......"
  connection.execute(q, data)
  
def import_bulk(datalist):
  with engine.connect() as connection:
    for data in datalist:
      import_single(connection, data)

alastair

postgres=# \dn
  List of schemas
  Name  |  Owner   
--------+----------
 public | postgres

alastair

musicbrainz_db=> \?
musicbrainz_db=> \dn
         List of schemas
       Name        |    Owner    
-------------------+-------------
 cover_art_archive | musicbrainz
 documentation     | musicbrainz
 musicbrainz       | musicbrainz
 public            | postgres
 report            | musicbrainz

alastair

Tables

db=> \d song
Table "public.song"
 Column | Type | Modifiers 
--------+------+-----------
 songid | text | not null
Indexes:
    "song_ndx_songid" UNIQUE, btree (songid)

alastair

[program:musicbrainz]
environment=PERL_LOCAL_LIB_ROOT="/home/musicbrainz/perl5",PERL_MB_OPT="--install_base /home/musicbrainz/perl5",PERL_MM_OPT="INSTALL_BASE=/home/musicbrainz/perl5",PERL5LIB="/home/musicbrainz/perl5/lib/perl5/x86_64-linux-gnu-thread-multi:/home/musicbrainz/perl5/lib/perl5",PATH="/home/musicbrainz/perl5/bin:$PATH"
command=/home/musicbrainz/perl5/bin/plackup -Ilib -s FCGI --listen fcgi.socket -r
directory=/home/musicbrainz/musicbrainz-server
autostart=true
autorestart=true
redirect_stderr=true
user=musicbrainz

alastair

acousticbrainz=# explain                                                                                                          select hl.mbid, hlm.data->>'value' as genre from highlevel hl join highlevel_model hlm on hl.id=hlm.highlevel where hlm.model=3;
                                                    QUERY PLAN
------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=211684.41..3159109.63 rows=3543041 width=206)
   Hash Cond: (hlm.highlevel = hl.id)
   ->  Bitmap Heap Scan on highlevel_model hlm  (cost=66319.13..2728196.53 rows=3543041 width=194)
         Recheck Cond: (model = 3)
         ->  Bitmap Index Scan on model_highlevel_ndx_highlevel_model  (cost=0.00..65433.37 rows=3543041 width=0)
               Index Cond: (model = 3)
   ->  Hash  (cost=79800.68..79800.68 rows=3571168 width=20)

alastair

acousticbrainz=# explain select hl.mbid, hlm.data from highlevel_model hlm join highlevel hl on hlm.highlevel=hl.id where model=3;
                                               QUERY PLAN
--------------------------------------------------------------------------------------------------------
 Hash Join  (cost=211900.41..3150468.03 rows=3543041 width=206)
   Hash Cond: (hlm.highlevel = hl.id)
   ->  Bitmap Heap Scan on highlevel_model hlm  (cost=66535.13..2728412.53 rows=3543041 width=194)
         Recheck Cond: (model = 3)
         ->  Bitmap Index Scan on model_ndx_highlevel_model  (cost=0.00..65649.37 rows=3543041 width=0)
               Index Cond: (model = 3)
   ->  Hash  (cost=79800.68..79800.68 rows=3571168 width=20)

alastair

musicbrainz_db=> select * from link_attribute_credit;


  link  | attribute_type |                                                                                                                    credited_as                                                                                                                    
--------+----------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 186492 |             12 | Chorus
 186493 |            461 | Gaya
 199487 |            180 | Yamaha and Steinway pianos
 186496 |            180 | Steinway piano
 186496 |            315 | 奄美三線