643fc5346b9416e784bdf9b169ae9405d651264d
hiram
  Thu Jul 30 15:37:45 2026 -0700
definitions to load the assembly_summary files from NCBI refs #37958

diff --git src/hg/lib/assemblySummary.sql src/hg/lib/assemblySummary.sql
new file mode 100644
index 00000000000..8281a7450fa
--- /dev/null
+++ src/hg/lib/assemblySummary.sql
@@ -0,0 +1,51 @@
+# assemblySummary.sql was originally generated by the autoSql program, which also
+# generated assemblySummary.c and assemblySummary.h.  This creates the database representation of
+# an object which can be loaded and saved from RAM in a fairly
+# automatic way.
+
+#NCBI assembly_summary data, see: https://ftp.ncbi.nlm.nih.gov/genomes/ASSEMBLY_REPORTS/README_assembly_summary.txt
+CREATE TABLE assemblySummary (
+    assemblyAccession varchar(20),	# www.ncbi.nlm.nih.gov/assembly/?term=xxx
+    bioproject varchar(20),	# www.ncbi.nlm.nih.gov/bioproject/?term=xxx
+    biosample varchar(20),	# www.ncbi.nlm.nih.gov/biosample/?term=xxx
+    wgsMaster varchar(20),	# www.ncbi.nlm.nih.gov/nuccore/xxx
+    refseqCategory varchar(30),	# representative or reference
+    taxId int unsigned,	# www.ncbi.nlm.nih.gov/taxonomy/?term=xxx
+    speciesTaxid int unsigned,	# www.ncbi.nlm.nih.gov/taxonomy/?term=xxx
+    organismName varchar(500),	# binomial scientific name
+    infraspecificName varchar(255),	# strain/cultivar/ecotype/breed
+    isolate varchar(255),	# source of sample
+    versionStatus varchar(20),	# latest/suppressed/replaced
+    assemblyLevel varchar(20),	# Contig/Scaffold/Complete Genome/Chromosome
+    releaseType varchar(20),	# Major/Minor/Patch
+    genomeRep varchar(20),	# Full/Partial
+    seqRelDate date,	# date YYYY-MM-DD sequence released to INSDC
+    asmName varchar(255),	# submitter supplied name
+    asmSubmitter varchar(500),	# institution submitting assembly
+    gbrsPairedAsm varchar(20),	# GenBank<->RefSeq GCA/GCF relationship
+    pairedAsmComp varchar(20),	# identical/different for GCA<->GCF relationship
+    ftpPath varchar(500),	# ftp.ncbi.nlm.nih.gov/genomes/all/GCx/012/345/678/asmId
+    excludedFromRefseq text,	# noted reason for exclusion from RefSeq
+    relationToTypeMaterial varchar(255),	# note of assembly relation to sample
+    asmNotLiveDate date,	# date YYYY-MM-DD assembly suppressed/replaced -- historical files only
+    assemblyType varchar(20),	# haploid/diploid/haploid-with-alt-loci/alternate-pseudohaplotype
+    taxonGroup varchar(30),	# bacteria/viral/archaea/fungi/metagenomes/invertebrate/other/vertebrate_other/plant/vertebrate_mammalian/protozoa
+    genomeSize bigint unsigned,	# total length of all top-level sequences in the primary assembly
+    genomeSizeUngapped bigint unsigned,	# genome length not counting gaps (gap == 10 or more Ns)
+    gcPercent decimal(7,4),	# GC percent
+    repliconCount int unsigned,	# total number of chromosomes, organelle genomes and plasmids
+    scaffoldCount int unsigned,	# number of scaffolds: placed, unlocalized, unplaced, alternate loci and patch
+    contigCount int unsigned,	# number of contigs in the primary assembly
+    annotationProvider varchar(500),	# the group that provided the annotation on the assembly
+    annotationName varchar(500),	# the name of the annotation
+    annotationDate date,	# annotation date YYYY-MM-DD
+    totalGeneCount int unsigned,	# total gene count in annotation
+    proteinCodingGeneCount int unsigned,	# protein coding gene count in annotation
+    nonCodingGeneCount int unsigned,	# non coding gene count in annotation
+    pubmedId text,	# semicolon separated list of PubMed ID(s)
+              #Indices	  PRIMARY KEY (assemblyAccession),
+    INDEX(taxId),	  KEY idxTaxid (taxid),
+    INDEX(organismName(100)),	  KEY idxOrganismName (organismName(100)),
+    INDEX(assemblyLevel),	  KEY idxTaxonGroup (taxonGroup),
+    INDEX(taxonGroup)	  KEY idxAssemblyLevel (assemblyLevel)
+);	) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;