Change Details

softwareheritage=> select mimetype, count, percent from swh_content_mimetype_text_repartition(); mimetype | count | percent ------------------------------------+---------+----------- text/plain | 2723484 | 37.3825 text/x-c | 1578897 | 21.6719 text/x-po | 583554 | 8.0098 text/html | 457466 | 6.2792 text/x-c++ | 392656 | 5.3896 application/xml | 375774 | 5.1579 text/x-php | 312741 | 4.2927 text/x-ruby | 275558 | 3.7823 text/x-python | 220031 | 3.0201 text/x-shellscript | 32886 | .4514 text/x-makefile | 20387 | .2798 text/x-asm | 11645 | .1598 image/svg+xml | 9916 | .1361 text/x-diff | 8405 | .1154 text/x-pascal | 7720 | .1060 text/troff | 7219 | .0991 text/x-lisp | 5607 | .0770 text/x-m4 | 4711 | .0647 text/x-tex | 4315 | .0592 application/javascript | 2746 | .0377 text/x-msdos-batch | 2336 | .0321 text/x-fortran | 2099 | .0288 text/x-perl | 1965 | .0270 application/x-elc | 525 | .0072 application/zlib | 507 | .0070 message/rfc822 | 254 | .0035 application/postscript | 227 | .0031 image/x-xpmi | 141 | .0019 text/rtf | 126 | .0017 application/vnd.ms-fontobject | 93 | .0013 text/x-lua | 93 | .0013 text/x-awk | 88 | .0012 application/pdf | 81 | .0011 application/x-setupscript | 56 | .0008 application/pgp-keys | 43 | .0006 message/news | 41 | .0006 text/x-tcl | 34 | .0005 image/x-portable-pixmap | 30 | .0004 application/pgp-signature | 29 | .0004 text/x-vcard | 21 | .0003 video/quicktime | 10 | .0001 application/mac-binhex40 | 5 | .0001 image/x-portable-greymap | 5 | .0001 application/vnd.font-fontforge-sfd | 4 | .0001 image/gif | 3 | .0000 application/x-wine-extension-ini | 3 | .0000 application/x-bzip2 | 3 | .0000 application/x-freemind | 3 | .0000 image/x-portable-bitmap | 2 | .0000 model/vrml | 2 | .0000 application/vnd.fdf | 1 | .0000 application/x-archive | 1 | .0000 application/vnd.ms-opentype | 1 | .0000 application/pgp | 1 | .0000 application/x-kdelnk | 1 | .0000 audio/mpeg | 1 | .0000 text/x-nawk | 1 | .0000 (57 rows) softwareheritage=> select count(*) from content_mimetype; count --------- 7285456 (1 row) # sql create type swh_content_mimetype_text_repartition_signature as ( mimetype text, count bigint, percent text ); create or replace function swh_content_mimetype_text_repartition() returns setof swh_content_mimetype_text_repartition_signature language plpgsql as $$ declare total real; begin select count(id) from content_mimetype into total; return query select convert_from(mimetype, 'utf-8'), count(id) as count, to_char((100.0 * count(id) / total), '999.9999') as percent from content_mimetype where encoding <> 'binary' group by mimetype order by count desc; end $$;

softwareheritage=> select mimetype, count, percent from swh_content_mimetype_text_repartition(); mimetype | count | percent ------------------------------------+---------+----------- text/plain | 2764656 | 37.3915 text/x-c | 1603002 | 21.6803 text/x-po | 593128 | 8.0220 text/html | 464516 | 6.2825 text/x-c++ | 398550 | 5.3903 application/xml | 379255 | 5.1294 text/x-php | 317574 | 4.2951 text/x-ruby | 279675 | 3.7826 text/x-python | 223713 | 3.0257 text/x-shellscript | 33402 | .4518 text/x-makefile | 20722 | .2803 text/x-asm | 11816 | .1598 image/svg+xml | 9961 | .1347 text/x-diff | 8486 | .1148 text/x-pascal | 7876 | .1065 text/troff | 7285 | .0985 text/x-lisp | 5694 | .0770 text/x-m4 | 4804 | .0650 text/x-tex | 4388 | .0593 application/javascript | 2811 | .0380 text/x-msdos-batch | 2380 | .0322 text/x-fortran | 2125 | .0287 text/x-perl | 2001 | .0271 application/x-elc | 531 | .0072 application/zlib | 518 | .0070 message/rfc822 | 259 | .0035 application/postscript | 227 | .0031 image/x-xpmi | 142 | .0019 text/rtf | 128 | .0017 application/vnd.ms-fontobject | 95 | .0013 text/x-lua | 94 | .0013 text/x-awk | 89 | .0012 application/pdf | 81 | .0011 application/x-setupscript | 57 | .0008 application/pgp-keys | 44 | .0006 message/news | 42 | .0006 text/x-tcl | 35 | .0005 image/x-portable-pixmap | 31 | .0004 application/pgp-signature | 29 | .0004 text/x-vcard | 21 | .0003 video/quicktime | 10 | .0001 application/mac-binhex40 | 5 | .0001 image/x-portable-greymap | 5 | .0001 application/vnd.font-fontforge-sfd | 4 | .0001 image/gif | 3 | .0000 application/x-wine-extension-ini | 3 | .0000 application/x-bzip2 | 3 | .0000 application/x-freemind | 3 | .0000 image/x-portable-bitmap | 2 | .0000 model/vrml | 2 | .0000 application/vnd.fdf | 1 | .0000 application/x-archive | 1 | .0000 application/vnd.ms-opentype | 1 | .0000 application/pgp | 1 | .0000 application/x-kdelnk | 1 | .0000 audio/mpeg | 1 | .0000 text/x-nawk | 1 | .0000 (57 rows) softwareheritage=> select (100 * (select count(*) from content_mimetype where encoding <> 'binary')) / (select count(*) from content_mimetype)::real as percent; percent ------------------ 96.7064603246392 (1 row) softwareheritage=> select count(*) from content_mimetype; count --------- 7393808 (1 row) softwareheritage=> select count(*) from content_mimetype where encoding <> 'binary'; count --------- 7150290 (1 row) softwareheritage=> select count(*) from content_language; count --------- 7017866 (1 row) # sql create type swh_content_mimetype_text_repartition_signature as ( mimetype text, count bigint, percent text ); create or replace function swh_content_mimetype_text_repartition() returns setof swh_content_mimetype_text_repartition_signature language plpgsql as $$ declare total real; begin select count(id) from content_mimetype into total; return query select convert_from(mimetype, 'utf-8'), count(id) as count, to_char((100.0 * count(id) / total), '999.9999') as percent from content_mimetype where encoding <> 'binary' group by mimetype order by count desc; end $$;

softwareheritage=> select mimetype, count, percent from swh_content_mimetype_text_repartition(); mimetype | count | percent ------------------------------------+---------+----------- text/plain | 272348464656 | 37.3825915 text/x-c | 1578897603002 | 21.6719803 text/x-po | 58355493128 | 8.0098220 text/html | 4574664516 | 6.2792825 text/x-c++ | 3926568550 | 5.3896903 application/xml | 3757749255 | 5.1579294 text/x-php | 3127417574 | 4.292751 text/x-ruby | 2755589675 | 3.78236 text/x-python | 2200313713 | 3.020157 text/x-shellscript | 328863402 | .45148 text/x-makefile | 20387722 | .2798803 text/x-asm | 11645 |816 | .1598 image/svg+xml | 991661 | .136147 text/x-diff | 840586 | .115448 text/x-pascal | 7720876 | .10605 text/troff | 721985 | .099185 text/x-lisp | 5607 |94 | .0770 text/x-m4 | 4711804 | .064750 text/x-tex | 431588 | .05923 application/javascript | 2746811 | .037780 text/x-msdos-batch | 233680 | .03212 text/x-fortran | 2099125 | .02887 text/x-perl | 19652001 | .02701 application/x-elc | 525 |31 | .0072 application/zlib | 507 |18 | .0070 message/rfc822 | 254 |9 | .0035 application/postscript | 227 | .0031 image/x-xpmi | 141 |2 | .0019 text/rtf | 126 |8 | .0017 application/vnd.ms-fontobject | 93 |5 | .0013 text/x-lua | 93 |4 | .0013 text/x-awk | 88 |9 | .0012 application/pdf | 81 | .0011 application/x-setupscript | 56 |7 | .0008 application/pgp-keys | 43 |4 | .0006 message/news | 41 |2 | .0006 text/x-tcl | 34 |5 | .0005 image/x-portable-pixmap | 30 |1 | .0004 application/pgp-signature | 29 | .0004 text/x-vcard | 21 | .0003 video/quicktime | 10 | .0001 application/mac-binhex40 | 5 | .0001 image/x-portable-greymap | 5 | .0001 application/vnd.font-fontforge-sfd | 4 | .0001 image/gif | 3 | .0000 application/x-wine-extension-ini | 3 | .0000 application/x-bzip2 | 3 | .0000 application/x-freemind | 3 | .0000 image/x-portable-bitmap | 2 | .0000 model/vrml | 2 | .0000 application/vnd.fdf | 1 | .0000 application/x-archive | 1 | .0000 application/vnd.ms-opentype | 1 | .0000 application/pgp | 1 | .0000 application/x-kdelnk | 1 | .0000 audio/mpeg | 1 | .0000 text/x-nawk | 1 | .0000 (57 rows) softwareheritage=> select (100 * (select count(*) from content_mimetype where encoding <> 'binary')) / (select count(*) from content_mimetype)::real as percent; percent ------------------ 96.7064603246392 (1 row) softwareheritage=> select count(*) from content_mimetype; count --------- 72854567393808 (1 row) softwareheritage=> select count(*) from content_mimetype where encoding <> 'binary'; count --------- 7150290 (1 row) softwareheritage=> select count(*) from content_language; count --------- 7017866 (1 row) # sql create type swh_content_mimetype_text_repartition_signature as ( mimetype text, count bigint, percent text ); create or replace function swh_content_mimetype_text_repartition() returns setof swh_content_mimetype_text_repartition_signature language plpgsql as $$ declare total real; begin select count(id) from content_mimetype into total; return query select convert_from(mimetype, 'utf-8'), count(id) as count, to_char((100.0 * count(id) / total), '999.9999') as percent from content_mimetype where encoding <> 'binary' group by mimetype order by count desc; end $$;