# Copyright (C) 2008 Wes Hardaker # License: Same as perl. See the LICENSE file for details. package Ham::Callsign::DB::US; use Ham::Callsign::DB; use Ham::Callsign; our @ISA = qw(Ham::Callsign::DB); use strict; sub init { my ($self) = @_; # maybe use dbh to prepare a few things } sub do_load_data { my ($self, $place) = @_; $self->{'import_place'} = $place; $self->insert_stuff('HD', 50); $self->insert_stuff('EN', 27); $self->insert_stuff('AM', 18); $self->insert_stuff('VC', 6); } sub do_lookup { my ($self, $callsign) = @_; if (!$self->{'lookupvc'}) { $self->{'lookupvc'} = $self->{'dbh'}->prepare(" select vc.callsign_requested as thecallsign, * from PUBACC_VC as vc left join PUBACC_HD as hd on hd.unique_system_identifier = vc.unique_system_identifier left join PUBACC_EN as en on en.unique_system_identifier = vc.unique_system_identifier left join PUBACC_AM as am on am.unique_system_identifier = vc.unique_system_identifier where vc.callsign_requested = ? order by vc.unique_system_identifier desc limit 1 "); $self->{'lookuphd'} = $self->{'dbh'}->prepare(" select hd.call_sign as thecallsign, * from PUBACC_HD as hd left join PUBACC_VC as vc on vc.unique_system_identifier = hd.unique_system_identifier left join PUBACC_EN as en on en.unique_system_identifier = hd.unique_system_identifier left join PUBACC_AM as am on am.unique_system_identifier = hd.unique_system_identifier where hd.call_sign = ? order by hd.unique_system_identifier desc limit 1 "); } $self->{'lookupvc'}->execute($callsign); my $row1 = $self->{'lookupvc'}->fetchrow_hashref; $self->{'lookupvc'}->finish; $self->{'lookuphd'}->execute($callsign); my $row2 = $self->{'lookuphd'}->fetchrow_hashref; $self->{'lookuphd'}->finish; my @results; if ($row1 && $row2 && !$self->{'USmultiple'}) { # take the most recent ULS file number if ($row1->{'uls_file_number'} > $row2->{'uls_file_number'}) { $row2 = undef; } else { $row1 = undef; } } if ($row1) { $row1->{'qth'} = "$row1->{city}, $row1->{state}, USA"; push @results,new Ham::Callsign(%$row1) } if ($row2) { $row2->{'qth'} = "$row2->{city}, $row2->{state}, USA"; push @results,new Ham::Callsign(%$row2) if ($row2); } map { $_->{'FromDB'} = 'US' } @results; return \@results; } # from fcc docs: # EN: Names and Addresses # HD: Main form 605 that carries over to license # AM: Amature data # VC: Vanity Callsign # # data can be joined "whenever the column name is the same in both tables" # - primary column for joining is the call sign # - joining for application data is the ULS file number (we don't load apps) # - Each application and license has been assigned a unique 9-digit system id # This is useful in cases where a call sign has been reassigned. sub do_create_tables { my ($self) = @_; my $dbh = $self->{'dbh'}; # These SQL statements were pulled from the FCC documentation at # http://wireless.fcc.gov/uls/index.htm?job=transaction&page=weekly # the FCC updates their schema on a regular basis and this module # will need to track those changes. $dbh->do("drop table PUBACC_EN"); $dbh->do("create table PUBACC_EN ( record_type char(2) not null, unique_system_identifier numeric(9,0) not null, uls_file_number char(14) null, ebf_number varchar(30) null, call_sign char(10) null, entity_type char(2) null, licensee_id char(9) null, entity_name varchar(200) null, first_name varchar(20) null, mi char(1) null, last_name varchar(20) null, suffix char(3) null, phone char(10) null, fax char(10) null, email varchar(50) null, street_address varchar(60) null, city varchar(20) null, state char(2) null, zip_code char(9) null, po_box varchar(20) null, attention_line varchar(35) null, sgin char(3) null, frn char(10) null, applicant_type_code char(1) null, applicant_type_other char(40) null, status_code char(1) null, status_date datetime null )"); $dbh->do("CREATE index callsign_index_en_id on PUBACC_EN(unique_system_identifier)"); $dbh->do("drop table PUBACC_AM"); $dbh->do("create table PUBACC_AM ( record_type char(2) not null, unique_system_identifier numeric(9,0) not null, uls_file_num char(14) null, ebf_number varchar(30) null, callsign char(10) null, operator_class char(1) null, group_code char(1) null, region_code tinyint null, trustee_callsign char(10) null, trustee_indicator char(1) null, physician_certification char(1) null, ve_signature char(1) null, systematic_callsign_change char(1) null, vanity_callsign_change char(1) null, vanity_relationship char(12) null, previous_callsign char(10) null, previous_operator_class char(1) null, trustee_name varchar(50) null ) "); $dbh->do("CREATE index callsign_index_am_id on PUBACC_AM(unique_system_identifier)"); $dbh->do("drop table PUBACC_HD"); $dbh->do("create table PUBACC_HD ( record_type char(2) not null, unique_system_identifier numeric(9,0) not null, uls_file_number char(14) null, ebf_number varchar(30) null, call_sign char(10) null, license_status char(1) null, radio_service_code char(2) null, grant_date char(10) null, expired_date char(10) null, cancellation_date char(10) null, eligibility_rule_num char(10) null, applicant_type_code_reserved char(1) null, alien char(1) null, alien_government char(1) null, alien_corporation char(1) null, alien_officer char(1) null, alien_control char(1) null, revoked char(1) null, convicted char(1) null, adjudged char(1) null, involved_reserved char(1) null, common_carrier char(1) null, non_common_carrier char(1) null, private_comm char(1) null, fixed char(1) null, mobile char(1) null, radiolocation char(1) null, satellite char(1) null, developmental_or_sta char(1) null, interconnected_service char(1) null, certifier_first_name varchar(20) null, certifier_mi char(1) null, certifier_last_name varchar(20) null, certifier_suffix char(3) null, certifier_title char(40) null, gender char(1) null, african_american char(1) null, native_american char(1) null, hawaiian char(1) null, asian char(1) null, white char(1) null, ethnicity char(1) null, effective_date char(10) null, last_action_date char(10) null, auction_id int null, reg_stat_broad_serv char(1) null, band_manager char(1) null, type_serv_broad_serv char(1) null, alien_ruling char(1) null, licensee_name_change char(1) null )"); $dbh->do("CREATE index callsign_index_hd_id on PUBACC_HD(unique_system_identifier)"); $dbh->do("CREATE index callsign_index_HD_SIGN on PUBACC_HD(call_sign)"); $dbh->do("drop table PUBACC_VC"); $dbh->do("create table PUBACC_VC ( record_type char(2) null, unique_system_identifier numeric(9,0) not null, uls_file_number char(14) null, ebf_number varchar(30) null, request_sequence int null, callsign_requested char(10) null ) "); $dbh->do("CREATE index callsign_index_VC_SIGN on PUBACC_VC(callsign_requested)"); $dbh->do("CREATE index callsign_index_vc_id on PUBACC_VC(unique_system_identifier)"); } ######################################## # DB insertion code # # split doesn't always return the right number of entries... it # chomps the end for some reason sometimes. This array fills in the blanks. # # XXX: this is likely a problem for some other reason and this is a # possibly broken hack. my @fill; for (my $i = 1; $i <= 100; $i++) { for (my $j = 0; $j < $i; $j++) { push @{$fill[$i]}, ''; } } # a generic function that inserts $num rows into a table sub insert_stuff { my ($self, $suffix, $num) = @_; my $count = 0; my @parts; my $dbh = $self->{'dbh'}; my $sth = $dbh->prepare("insert into PUBACC_$suffix values(" . ("?, " x ($num-1)). " ?)"); $| = 1; print "starting $suffix\n"; open(I,"<$self->{'import_place'}/$suffix.dat"); $dbh->begin_work(); while () { chomp; s/\r//; @parts = split(/\|/); # print "parts: $_\n " . join(",",@parts). "\n" if ($count == 0); my $diff = $num - $#parts - 1; $sth->execute(@parts, @{$fill[$diff]}); # $sth->execute(@parts); $count++; if ($count % 10000 == 0) { print "."; $dbh->commit(); $dbh->begin_work(); } } $dbh->commit(); print "inserted: $count rows into $suffix\n"; } 1; =pod =head1 NAME =cut