[Brainstorm / Please participate!] A thought to speed up checking with json/sql

[spirillen]

Intro

Brainstorm, to speedup the checks and maybe a few new thoughts...

If i take a peak in the SQL, there is a nice table called pyfuncle_whois and it's equlant in json is whois_db.json

Test flow

Now one of my thought on the test-flow is:

Why are domains not default verified against this data to see if we actually need to re-check the ACTIVE state? If expires is in the future, skip further testing. This should take milliseconds.

Let take a real ex. from a project I'm helping in it's start: https://travis-ci.com/Import-External-Sources/pornhosts/builds

Now add óne new domain to the https://github.com/Import-External-Sources/pornhosts/blob/master/source/hosts.txt and the CI/CD will be running 5 or 6 cycles ~ 1½ hour for testing(adding) 1 new submission. but testing the domain manually takes 0m0,572s

WHOIS domain.tld

time whois xxx.vivian.jp
[ JPRS database provides information on network administration. Its use is    ]
[ restricted to network administration purposes. For further information,     ]
[ use 'whois -h whois.jprs.jp help'. To suppress Japanese output, add'/e'     ]
[ at the end of command, e.g. 'whois -h whois.jprs.jp xxx/e'.                 ]

No match!!

With JPRS WHOIS, you can query the following domain name information
sponsored by JPRS.
    - All of registered JP domain name
    - gTLD domain name of which sponsoring registrar is JPRS
Detail: https://jprs.jp/about/dom-search/jprs-whois/ (only in Japanese)

For IP address information, please refer to the following WHOIS servers:
    - JPNIC WHOIS (whois.nic.ad.jp)
    - APNIC WHOIS (whois.apnic.net)
    - ARIN WHOIS (whois.arin.net)
    - RIPE WHOIS (whois.ripe.net)
    - LACNIC WHOIS (whois.lacnic.net)
    - AfriNIC WHOIS (whois.afrinic.net)


real    0m0,572s
user    0m0,006s
sys     0m0,000s

So what if a user would like actually do a specified dns or http testing? for inactive there's already the -dbr $days (Which i think should be -dbe e for expired there should be something like:

-dba : Recheck ACTIVE requires using one of ==>
-RcodeA(AAA)? : Validate A and AAAA
-httpv : Validate http(s)?

The thought about this is flexibility for the individual users setup and needs.
But for my need, I actually don't care whether a blocked domain is responding or not, because it can be responding 0,001 msec after my test

SQL

The next idea is based on how the DB is loaded on a CI startup... (note haven't checked the code on this)

But while running in a SQL environment the easiest is to handle a SQL file like pyfunceble.sql fir export/import on external DB's straight with the mysql-client(dump).

CI/CD

This should be loaded before test to satisfy above suggestion, and dump after testing to do first statement in next run

Finishing note

These thought jump into my head on the jar 🚽 so forgive me if there are some thought on this in other issues, as I jumped to write this while it is fresh in mind.

Share

Please other users of PyFunceble, please share you comment on this, they are left as brainstorm/thoughts/idea

PS:

Using the option command travis should be renamed to ci or cd as PyFunceble actually functions pretty well on other cd/cd than travis 😲

[funilrys]

Why are domains not default verified against this data to see if we actually need to re-check the ACTIVE state? If expires is in the future, skip further testing. This should take milliseconds.

We actually do that... After checking for the syntax 😉
The code:

• Gather expiration date from the database (The function only provide it if present and it's in the future.)

  • PyFunceble/PyFunceble/status/availability/domain_and_ip.py

    Lines 88 to 99 in 65e1801

    	if not self.status.whois_server:
    	return None, None
    	if self.whois_db:
    	expiration_date_from_database = self.whois_db.get_expiration_date(
    	self.subject
    	)
    	else:
    	expiration_date_from_database = None
    	if expiration_date_from_database:
    	return expiration_date_from_database, "DATE EXTRACTED FROM WHOIS DATABASE"

• The function itself:

  • PyFunceble/PyFunceble/database/whois.py

    Lines 337 to 361 in 65e1801

    	def get_expiration_date(self, subject):
    	"""
    	Get the expiration date of the given subject.
    	:param str subject: The subject we are working with.
    	:return: The expiration date from the database.
    	:rtype: str|None
    	"""
    	if self.authorized and self[subject] and not self.is_time_older(subject):
    	# * We are authorized to work.
    	# and
    	# * The element we are testing is in the database.
    	# and
    	# * The expiration date is in the future.
    	try:
    	# We return the expiration date.
    	return self[subject]["expiration_date"]
    	except KeyError: # pragma: no cover
    	pass
    	# We return None, there is no data to work with.
    	return None

So what if a user would like actually do a specified dns or http testing?

Can you extend/explain what you mean with that ?

But while running in a SQL environment the easiest is to handle a SQL file like pyfunceble.sql fir export/import on external DB's straight with the mysql-client(dump).

PyFunceble already do that for us but yeah it's a way to gain some time 😉

Using the option command travis should be renamed to ci or cd as PyFunceble actually functions pretty well on other cd/cd than travis

Nope, autosaving with git and a few other things won't necessarily work! That's why on my list there is the test and implementation of other CI/CD infrastructure like the one from GitLab 😄

It's not for nothing that I put the milestone 3.0.0 to January 1st 2020 😸 It's my deadline! Of course, I can release before that and I'll probably do once I feel confident about it.
The idea is to implement as much as possible, fix as much as possible until the day I can confidently say: Let's do it folk! 😉

[spirillen]

So what if a user would like actually do a specified dns or http testing?

Can you extend/explain what you mean with that ?

Sure, If you take a look from: https://travis-ci.com/Import-External-Sources/google.tld/jobs/257187392#L414 and https://travis-ci.com/Import-External-Sources/pornhosts/jobs/257274300#L504

From my needs:

1. Check syntax of submission
2. Rewrite to IDNA (for DNS Zone to work 100% on at least Bind and PowerDNS)
3. We know it's expires in future (Stop further testing, it's an ACTIVE with expires date in future, and we just checked it 5 mins ago)
4. if DNS request is needed use local pdns-recursor
5. If INACTIVE retest in future -dbr $days

What is done.

1. Domain is ACTIVE
2. Do DNSLOOKUP (Why?, we already concluded it's an ACTIVE to block)
3. Do HTTP response code, again why?

For the above I haven't found a switch to NOT doing test 2. or 3. and only do the whois and the syntax test.
The --idna and --syntax is the reason to run all submissions through the PyFunceble, as humans get lazy and makes mistakes)

Other purpose of controlling test protocols....

rCode

If you are running a DNS server/Resolver that is meant to block with ex.:

1. rCode NXDOMAIN
2. rCode A 127.0.0.1
3. rCode AAAA ::1

Now you can explicit test if that is running as you think it should.

--dns 127.0.0.1 -rcode NXDOMAIN,127.0.0.1,::1

where --rcode "varible" is = false output as ACTIVE ( you need to do something active to succeed your goal )

Now all failures will comes out as ACTIVE (which in this case is errors)

the above use case of RCODE in a if else fi

IF RCODE == NXDOMAIN|EMPTY|etc.
then
    mark as INACTIVE
ELSE
     mark as ACTIVE
fi

http - ftp

If a user explicitly want to test against the http or ftp protocol you simply add this switch to the test string:
--http --ftp

Now PyFunceble is defaut only doing whois (expires) test and yet fully customizable for how you'll do sub-checks from whois, if this is something you feels the need for.

Reversed test

So how could PyFunceble be used in reversed test cases

1. A user submits a new record(s) to test and validate against syntax => IDNA=> expires => RESULTS is ACTIVE
2. Another test process, will now test the DNS/Hosts-file setup against the output from RESULTS
   1. For hosts: RESULTS => --http --ftp => ACTIVE as ERRORS
   2. For DNS: RESULTS => --dns 127.0.0.1 -rcode NXDOMAIN => ACTIVE as ERRORS
3. In test 2. you'll disable the whois lookup with the existing --wdb

On a local system I would of curse do the hosts/dns test in other ways ex. with diff on linux, but to do this by a CI, well it could be the lazy choice 😃

Nope, autosaving with git and a few other things won't necessarily work! That's why on my list there is the test and implementation of other CI/CD infrastructure like the one from GitLab

Hate to break it then, but it have been running on a assessor to https://gitlab.com/rpz-zones/google.tld 😃 and ok haven't tested it on Bitbucket at all. It will of curse make some nice sense if it only runs with confidence on travis

end note

Stopping the answer here, as I spent 1½ hours re-writing and yet again re-writing to make it better explained...

So please drop a comment if an answer is unclear

[spirillen]

From the test string from #61 add the -dbr 7 I was looking in the DB for a few *.bid domains

guess what, there are no such records as awgdgpawhwgi.bid which leads to a test-loop even after having testing it 5 minutes ago 😒

Wouldn't it speed up thing if ALL tested domains was recorded to re-fetch eg. last tested as ex. -dbr 5 becomes rather pointless if no data is stored.

Side note

grep -i ".bid$" output/domains/INACTIVE/list | wc -l
261

DB entries in pyfunceble_inactive = 0

Let's have another ex. where the test happens unexpected to me.
Same re-run (3rd time) then we have the domain hdwkcfqzxhvx.com
and a DB record like this

INSERT INTO PyFunceble.pyfunceble_whois (subject,expiration_date,expiration_date_epoch,state,record,digest,created,modified) VALUES 
('hdwkcfqzxhvx.com','05-may-2020',1588629600,'future','   Domain Name: HDWKCFQZXHVX.COM
   Registry Domain ID: 2387572425_DOMAIN_COM-VRSN
   Registrar WHOIS Server: whois.namecheap.com
   Registrar URL: http://www.namecheap.com
   Updated Date: 2019-05-05T01:15:23Z
   Creation Date: 2019-05-05T01:15:18Z
   Registry Expiry Date: 2020-05-05T01:15:18Z
   Registrar: NameCheap, Inc.
   Registrar IANA ID: 1068
   Registrar Abuse Contact Email: abuse@namecheap.com
   Registrar Abuse Contact Phone: +1.6613102107
   Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
   Name Server: NS0.DNSMADEEASY.COM
   Name Server: NS1.DNSMADEEASY.COM
   Name Server: NS2.DNSMADEEASY.COM
   Name Server: NS3.DNSMADEEASY.COM
   Name Server: NS4.DNSMADEEASY.COM
   DNSSEC: unsigned
   URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
>>> Last update of whois database: 2019-11-17T23:02:29Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

NOTICE: The expiration date displayed in this record is the date the
registrar''s sponsorship of the domain name registration in the registry is
currently set to expire. This date does not necessarily reflect the expiration
date of the domain name registrant''s agreement with the sponsoring
registrar.  Users may consult the sponsoring registrar''s Whois database to
view the registrar''s reported date of expiration for this registration.

TERMS OF USE: You are not authorized to access or query our Whois
database through the use of electronic processes that are high-volume and
automated except as reasonably necessary to register domain names or
modify existing registrations; the Data in VeriSign Global Registry
Services'' ("VeriSign") Whois database is provided by VeriSign for
information purposes only, and to assist persons in obtaining information
about or related to a domain name registration record. VeriSign does not
guarantee its accuracy. By submitting a Whois query, you agree to abide
by the following terms of use: You agree that you may use this Data only
for lawful purposes and that under no circumstances will you use this Data
to: (1) allow, enable, or otherwise support the transmission of mass
unsolicited, commercial advertising or solicitations via e-mail, telephone,
or facsimile; or (2) enable high volume, automated, electronic processes
that apply to VeriSign (or its computer systems). The compilation,
repackaging, dissemination or other use of this Data is expressly
prohibited without the prior written consent of VeriSign. You agree not to
use electronic processes that are automated and high-volume to access or
query the Whois database except as reasonably necessary to register
domain names or modify existing registrations. VeriSign reserves the right
to restrict your access to the Whois database in its sole discretion to ensure
operational stability.  VeriSign may restrict or terminate your access to the
Whois database for failure to abide by these terms of use. VeriSign
reserves the right to modify these terms at any time.

The Registry database contains ONLY .COM, .NET, .EDU domains and
Registrars.
','e96ec2e308f99637004d1f37eaaf0b5f9993f8c1d5617d2961bd82f13cfe7198','2019-11-18 00:02:37.0','2019-11-18 00:02:37.0')
;

yet it have been tested on all 4 runs.... with-in a couple of hours

pyfunceble -v
pyfunceble 2.45.0.dev (Green Galago: Skitterbug)

[funilrys]

guess what, there are no such records as awgdgpawhwgi.bid

With --filter ?

leads to a test-loop even after having testing it 5 minutes ago unamused

Patch on its way.

Wouldn't it speed up thing if ALL tested domains was recorded to re-fetch eg. last tested as ex. -dbr 5 becomes rather pointless if no data is stored.

It's true, no data is stored. The patch is on its way. Stupid restructuration/improvement ideas I got weeks ago...

[funilrys]

This tool is becoming bigger and bigger... I even get the nightmare of it 😂

I need to take a day off anything else and really refocus on the whole infrastructure before releasing it... The restructuration was successful but some of my optimization and improvement ideas went great or wrong in this case ...

[funilrys]

• Correction patch will be on its way, but not today (Berlin Time). I have to reconfigure/rebuild my development environment 😭

[spirillen]

Nope, no --filter as that would break my idea of running all submission through this tool.

This tool is becoming bigger and bigger... I even get the nightmare of it

🆒 🤶 Is near by to comfort you 😄

Year, it getting bigger, but also better... we 🤞 for this. The god part of this is, you have already developed the most of it. PyFunceble have come from the crawling age to it walking stage... just wait to it becomes a teenager 🤕

[spirillen]

Hi, just done a simple math on this run.... https://travis-ci.com/github/Import-External-Sources/pornhosts/builds. First run is Build 1921.

There is 9776 records in the file that needs to be tested, and at the current moment (build 1956) it have an average of testing 6,9 domains a minutes....

I thinks the reason for this is that every time a new Travis is started you Pyfunceble runs through the entire list from the beginning and not actually continues from where it left of.

An initial thought on this could be to set(flip) a value in the DB of which was just tested, which was added and where do we continue.

Just as examples, 0 = tested, 1 = Not tested, 2 = New, Not tested (added between runs).
If these values is set, then when you load the test-file, it will leave out any "known records" and if there should be a new record, add it to the test table with the value of 2, then read the DB for any records with the value of (!=|>) 0 and test those in the current run. And Travis doesn't have to start all over, because you add "1 to many" records while it is running.

update to add a bit more logic to the code flow 😄

[funilrys]

Good point @spirillen, the problem: I want a solution for both: MariaDB/MySQL and the JSON one.

I will think about this when I get a bit of a time 👍

[funilrys]

@AnonymousPoster, I removed it. The release is on its way out right now. (So will be available in the next 30-45 minutes if everything goes right)

@spirillen, if you still need it, feel free to use the --shadow argument or its configuration argument. (Documentation generation is on it's way too... So in the coming 5-10 minutes).

[funilrys]

We need to improve the way we scan/test a file 🤔 Any ideas @spirillen @AnonymousPoster ? I'm out of direct idea right now 🤔

https://travis-ci.com/github/dns-test/porn.host.rpz is an example of the whole process which may take days 🤔

[spirillen]

To continue our keybase talk in the open. Then here is a suggestion on how to change the DB architecture to use more caching and less UPDATE (delete - write) in the databases as it is destroying the cache on every update.

The current design looks something like this:

What I think is we should distribute this over several databases as it would prevent a lot I/O for rebuilding cache

So first we spread own information

The whois information (somewhat more stable)

In this we store all know WHOIS information, never delete(cleanup or so (of curse after 5 years or something))

In the next DB we store all domains ever tested as the PRIMARY key for everything

The url is here a subkey to the domain_id. In case the domain is expired, we can drop testing the url

This is the DB that requires a lot of resources

This is where we build the yet to test DB.
It is here we can limit what needs to be tested as it in theory should be rather "small", as we initially start by seeing last tested date vs -dbr value in the other (cached) DB's

And then a new one to meet the powerfull RPZ standard *.sucker.rpz-nsdname CNAME . etc.

This is where we can find the source of a domain "distribution" and find similarities in the suckers DNS, as ex. parklogic.com

These dumps is not fully completed, but thought as the base for the BIG idea

[spirillen]

To follow up with a few comments

The you currently store filepath in the pyfunceble_tested => give it it's own DB and then link domain/url to it's ID it fill and I/O expensive to store it 10000000000 times

The same for the WHOIS_server and tested

For the pyfunceble_whois you store the almost same information a million time. ex:

record
Domain Name: 00043138.COM
Registry Domain ID: 2329271659_DOMAIN_COM-VRSN
Registrar WHOIS Server: whois.godaddy.com
Registrar URL: http://www.godaddy.com
Updated Date: 2019-06-15T08:09:33Z
Creation Date: 2018-11-04T11:45:05Z
Registry Expiry Date: 2020-11-04T11:45:05Z
Registrar: GoDaddy.com, LLC
Registrar IANA ID: 146
Registrar Abuse Contact Email: abuse@godaddy.com
Registrar Abuse Contact Phone: 480-624-2505
Domain Status: clientDeleteProhibited https://icann.org/epp#clientDeleteProhibited
Domain Status: clientRenewProhibited https://icann.org/epp#clientRenewProhibited
Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
Domain Status: clientUpdateProhibited https://icann.org/epp#clientUpdateProhibited
Name Server: NS1.DNS.COM
Name Server: NS2.DNS.COM
DNSSEC: unsigned
URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/

Last update of whois database: 2020-05-01T13:49:39Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

NOTICE: The expiration date displayed in this record is the date the
registrar's sponsorship of the domain name registration in the registry is
currently set to expire. This date does not necessarily reflect the expiration
date of the domain name registrant's agreement with the sponsoring
registrar. Users may consult the sponsoring registrar's Whois database to
view the registrar's reported date of expiration for this registration.

TERMS OF USE: You are not authorized to access or query our Whois
database through the use of electronic processes that are high-volume and
automated except as reasonably necessary to register domain names or
modify existing registrations; the Data in VeriSign Global Registry
Services' ("VeriSign") Whois database is provided by VeriSign for
information purposes only, and to assist persons in obtaining information
about or related to a domain name registration record. VeriSign does not
guarantee its accuracy. By submitting a Whois query, you agree to abide
by the following terms of use: You agree that you may use this Data only
for lawful purposes and that under no circumstances will you use this Data
to: (1) allow, enable, or otherwise support the transmission of mass
unsolicited, commercial advertising or solicitations via e-mail, telephone,
or facsimile; or (2) enable high volume, automated, electronic processes
that apply to VeriSign (or its computer systems). The compilation,
repackaging, dissemination or other use of this Data is expressly
prohibited without the prior written consent of VeriSign. You agree not to
use electronic processes that are automated and high-volume to access or
query the Whois database except as reasonably necessary to register
domain names or modify existing registrations. VeriSign reserves the right
to restrict your access to the Whois database in its sole discretion to ensure
operational stability. VeriSign may restrict or terminate your access to the
Whois database for failure to abide by these terms of use. VeriSign
reserves the right to modify these terms at any time.

The Registry database contains ONLY .COM, .NET, .EDU domains and
Registrars.
|
| Domain Name: 00053138.COM
Registry Domain ID: 2329271771_DOMAIN_COM-VRSN
Registrar WHOIS Server: whois.godaddy.com
Registrar URL: http://www.godaddy.com
Updated Date: 2019-06-15T08:09:37Z
Creation Date: 2018-11-04T11:45:14Z
Registry Expiry Date: 2020-11-04T11:45:14Z
Registrar: GoDaddy.com, LLC
Registrar IANA ID: 146
Registrar Abuse Contact Email: abuse@godaddy.com
Registrar Abuse Contact Phone: 480-624-2505
Domain Status: clientDeleteProhibited https://icann.org/epp#clientDeleteProhibited
Domain Status: clientRenewProhibited https://icann.org/epp#clientRenewProhibited
Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
Domain Status: clientUpdateProhibited https://icann.org/epp#clientUpdateProhibited
Name Server: NS1.DNS.COM
Name Server: NS2.DNS.COM
DNSSEC: unsigned
URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/

Last update of whois database: 2020-05-01T13:49:39Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

NOTICE: The expiration date displayed in this record is the date the
registrar's sponsorship of the domain name registration in the registry is
currently set to expire. This date does not necessarily reflect the expiration
date of the domain name registrant's agreement with the sponsoring
registrar. Users may consult the sponsoring registrar's Whois database to
view the registrar's reported date of expiration for this registration.

TERMS OF USE: You are not authorized to access or query our Whois
database through the use of electronic processes that are high-volume and
automated except as reasonably necessary to register domain names or
modify existing registrations; the Data in VeriSign Global Registry
Services' ("VeriSign") Whois database is provided by VeriSign for
information purposes only, and to assist persons in obtaining information
about or related to a domain name registration record. VeriSign does not
guarantee its accuracy. By submitting a Whois query, you agree to abide
by the following terms of use: You agree that you may use this Data only
for lawful purposes and that under no circumstances will you use this Data
to: (1) allow, enable, or otherwise support the transmission of mass
unsolicited, commercial advertising or solicitations via e-mail, telephone,
or facsimile; or (2) enable high volume, automated, electronic processes
that apply to VeriSign (or its computer systems). The compilation,
repackaging, dissemination or other use of this Data is expressly
prohibited without the prior written consent of VeriSign. You agree not to
use electronic processes that are automated and high-volume to access or
query the Whois database except as reasonably necessary to register
domain names or modify existing registrations. VeriSign reserves the right
to restrict your access to the Whois database in its sole discretion to ensure
operational stability. VeriSign may restrict or terminate your access to the
Whois database for failure to abide by these terms of use. VeriSign
reserves the right to modify these terms at any time.

The Registry database contains ONLY .COM, .NET, .EDU domains and
Registrars.
|

What it does is eating up resources. Extract the basic needs and have the script output the reused information's.
And where the result is clientDeleteProhibited https://icann.org/epp#clientDeleteProhibited store it once and give it an ID it is to be cached 😄

[spirillen]

Here is a doublet in pyfunceble_auto_continue, same file, but two path

/storage01/repos/github/clefspeare13/pornhosts/submit_here/hosts.txt
/var/storage01/repos/github/clefspeare13/pornhosts/submit_here/hosts.txt

The only way out of this is to convert the test key to the domain, this will leads to retesting the same domain/url over and over again with in a very little and undesired time frame

Here is a couple of other examples, with same subject, different sources

id	file_path	subject	status	is_complement	digest	created	modified
142668	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	0--ass-cinema-newsp.da.ru	ACTIVE	0	516868e1a9f3a3757d4ab6330a1b2e47bd37477908de48ee60f8d40f17a1d6b5	2020-05-10 19:22:06	2020-05-10 19:22:06
386192	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/domains.list	0--ass-cinema-newsp.da.ru	ACTIVE	0	77d2e822c466d770ef31453b65558915fdf046819ad51030330d7e6a5e20c377	2020-06-08 17:36:19	2020-06-08 17:36:19
142676	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	0--fightingshaving.da.ru	ACTIVE	0	1e3f378a9d5fce2acffaef1df029f2958557bec8bb2c9c7c72902ebb4e8786ab	2020-05-10 19:22:07	2020-05-10 19:22:07
386198	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/domains.list	0--fightingshaving.da.ru	ACTIVE	0	32fac99da7dcff396133a874a1307788996f83eef6ed066b4319e801e67b75dd	2020-06-08 17:36:20	2020-06-08 17:36:20
142677	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	0--foodwarez.da.ru	ACTIVE	0	31932ef6eb37638f2b7662594487c6ecb9aed9e885ba3d29eeabc29b87b0a611	2020-05-10 19:22:07	2020-05-10 19:22:07
386195	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/domains.list	0--foodwarez.da.ru	ACTIVE	0	3dd3912cbb02ef9d2c605bf69a7284b0a8881620f0c79a40fd077476260dbe18	2020-06-08 17:36:20	2020-06-08 17:36:20
386103	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/domains.list	0-0-0-1blowjob.da.ru	ACTIVE	0	5fc015943130f157b7bcdb0e6d2fc2c55eacc8190402da8cff5a40b24ca43a4c	2020-06-08 17:35:07	2020-06-08 17:35:07
142585	/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	0-0-0-1blowjob.da.ru	ACTIVE	0	e0f7f3b8ee58de8d5f371cae3382cb31d0b90bc502ad668814f83d4b231f5639	2020-05-10 19:21:41	2020-05-10 19:21:41

And in sql it looks more like this

select file_path, subject, count(*) as occurrences
from PyFunceble.pyfunceble_auto_continue 
group by subject 
having count(*) > 1;

Top 10 results

file_path	subject	occurrences
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	bangbus.com	4
/var/repositories/bitbucket/hosts-file.net/hphosts_emd.txt	adultadworld.com	4
/var/storage01/repositories/gitlab/matrix/matrix/source/adware/domains.list	ads2.contentabc.com	4
hphosts_psh.txt	dk-media.s3.amazonaws.com	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	100oj.booru.org	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	3d.booru.org	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	angeladevi.free.fr	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	angela.ts.free.fr	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	angela.uw.hu	3
/storage01/repositories/gitlab/matrix/matrix/source/porno-sites/combined.txt	angelcat02.skyblog.com	3

You have the key so you can test it your self 😃

[spirillen]

For the @control-whois-storage --store-whois (#108)

Why is it you would like to keep the entire WHOISdump in the DB? It is a significant amount of data, which you can shopt down to needed information's and leave the rest of the whois information to templates, based on .tld https://en.wikipedia.org/wiki/Tld

Examples

info

whois azvjudwr.info
Domain Name: AZVJUDWR.INFO
Registry Domain ID: D503300000575448281-LRMS
Registrar WHOIS Server: whois.dynadot.com
Registrar URL: http://www.dynadot.com
Updated Date: 2020-02-05T13:57:43Z
Creation Date: 2019-02-19T11:16:34Z
Registry Expiry Date: 2021-02-19T11:16:34Z
Registrar Registration Expiration Date:
Registrar: Dynadot, LLC
Registrar IANA ID: 472
Registrar Abuse Contact Email: abuse@dynadot.com
Registrar Abuse Contact Phone: +1.6502620100
Reseller:
Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
Registrant Organization:
Registrant State/Province: California
Registrant Country: US
Name Server: NS1.CSOF.NET
Name Server: NS2.CSOF.NET
Name Server: NS4.CSOF.NET
Name Server: NS3.CSOF.NET
Name Server: NS5.CSOF.NET
Name Server: NS6.CSOF.NET
Name Server: NS7.CSOF.NET
Name Server: NS8.CSOF.NET
DNSSEC: unsigned
URL of the ICANN Whois Inaccuracy Complaint Form is https://www.icann.org/wicf/
>>> Last update of WHOIS database: 2020-08-28T13:35:12Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

Here you should strip the

URL of the ICANN Whois Inaccuracy Complaint Form is https://www.icann.org/wicf/
>>> Last update of WHOIS database: 2020-08-28T13:35:12Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

This is waste of space.

xyz

whois cdnfile.xyz
Domain Name: CDNFILE.XYZ
Registry Domain ID: D192222729-CNIC
Registrar WHOIS Server: whois.registrar.eu
Registrar URL: https://www.openprovider.com/
Updated Date: 2020-08-02T02:08:32.0Z
Creation Date: 2020-07-04T11:34:37.0Z
Registry Expiry Date: 2021-07-04T23:59:59.0Z
Registrar: Hosting Concepts B.V. d/b/a Openprovider
Registrar IANA ID: 1647
Domain Status: serverTransferProhibited https://icann.org/epp#serverTransferProhibited
Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
Registrant Organization:
Registrant State/Province: Berlin
Registrant Country: DE
Registrant Email: Please query the RDDS service of the Registrar of Record identified in this output for information on how to contact the Registrant, Admin, or Tech contact of the queried domain name.
Admin Email: Please query the RDDS service of the Registrar of Record identified in this output for information on how to contact the Registrant, Admin, or Tech contact of the queried domain name.
Tech Email: Please query the RDDS service of the Registrar of Record identified in this output for information on how to contact the Registrant, Admin, or Tech contact of the queried domain name.
Name Server: NS1.UPSERVER.XYZ
Name Server: NS2.UPSERVER.XYZ
DNSSEC: unsigned
Billing Email: Please query the RDDS service of the Registrar of Record identified in this output for information on how to contact the Registrant, Admin, or Tech contact of the queried domain name.
Registrar Abuse Contact Email: abuse@registrar.eu
Registrar Abuse Contact Phone: +31.104482297
URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
>>> Last update of WHOIS database: 2020-08-28T13:36:53.0Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

>>> IMPORTANT INFORMATION ABOUT THE DEPLOYMENT OF RDAP: please visit
https://www.centralnic.com/support/rdap <<<


Access to the Whois and RDAP services is rate limited. For more
information, visit https://registrar-console.centralnic.com/pub/whois_guidance.

Again waste of space to store

URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
>>> Last update of WHOIS database: 2020-08-28T13:36:53.0Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

>>> IMPORTANT INFORMATION ABOUT THE DEPLOYMENT OF RDAP: please visit
https://www.centralnic.com/support/rdap <<<


Access to the Whois and RDAP services is rate limited. For more
information, visit https://registrar-console.centralnic.com/pub/whois_guidance.

com

whois google.com
   Domain Name: GOOGLE.COM
   Registry Domain ID: 2138514_DOMAIN_COM-VRSN
   Registrar WHOIS Server: whois.markmonitor.com
   Registrar URL: http://www.markmonitor.com
   Updated Date: 2019-09-09T15:39:04Z
   Creation Date: 1997-09-15T04:00:00Z
   Registry Expiry Date: 2028-09-14T04:00:00Z
   Registrar: MarkMonitor Inc.
   Registrar IANA ID: 292
   Registrar Abuse Contact Email: abusecomplaints@markmonitor.com
   Registrar Abuse Contact Phone: +1.2083895740
   Domain Status: clientDeleteProhibited https://icann.org/epp#clientDeleteProhibited
   Domain Status: clientTransferProhibited https://icann.org/epp#clientTransferProhibited
   Domain Status: clientUpdateProhibited https://icann.org/epp#clientUpdateProhibited
   Domain Status: serverDeleteProhibited https://icann.org/epp#serverDeleteProhibited
   Domain Status: serverTransferProhibited https://icann.org/epp#serverTransferProhibited
   Domain Status: serverUpdateProhibited https://icann.org/epp#serverUpdateProhibited
   Name Server: NS1.GOOGLE.COM
   Name Server: NS2.GOOGLE.COM
   Name Server: NS3.GOOGLE.COM
   Name Server: NS4.GOOGLE.COM
   DNSSEC: unsigned
   URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
>>> Last update of whois database: 2020-08-28T13:37:01Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

NOTICE: The expiration date displayed in this record is the date the
registrar's sponsorship of the domain name registration in the registry is
currently set to expire. This date does not necessarily reflect the expiration
date of the domain name registrant's agreement with the sponsoring
registrar.  Users may consult the sponsoring registrar's Whois database to
view the registrar's reported date of expiration for this registration.

Domain Name: google.com
Registry Domain ID: 2138514_DOMAIN_COM-VRSN
Registrar WHOIS Server: whois.markmonitor.com
Registrar URL: http://www.markmonitor.com
Updated Date: 2019-09-09T08:39:04-0700
Creation Date: 1997-09-15T00:00:00-0700
Registrar Registration Expiration Date: 2028-09-13T00:00:00-0700
Registrar: MarkMonitor, Inc.
Registrar IANA ID: 292
Registrar Abuse Contact Email: abusecomplaints@markmonitor.com
Registrar Abuse Contact Phone: +1.2083895770
Domain Status: clientUpdateProhibited (https://www.icann.org/epp#clientUpdateProhibited)
Domain Status: clientTransferProhibited (https://www.icann.org/epp#clientTransferProhibited)
Domain Status: clientDeleteProhibited (https://www.icann.org/epp#clientDeleteProhibited)
Domain Status: serverUpdateProhibited (https://www.icann.org/epp#serverUpdateProhibited)
Domain Status: serverTransferProhibited (https://www.icann.org/epp#serverTransferProhibited)
Domain Status: serverDeleteProhibited (https://www.icann.org/epp#serverDeleteProhibited)
Registrant Organization: Google LLC
Registrant State/Province: CA
Registrant Country: US
Registrant Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Admin Organization: Google LLC
Admin State/Province: CA
Admin Country: US
Admin Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Tech Organization: Google LLC
Tech State/Province: CA
Tech Country: US
Tech Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Name Server: ns4.google.com
Name Server: ns3.google.com
Name Server: ns1.google.com
Name Server: ns2.google.com
DNSSEC: unsigned
URL of the ICANN WHOIS Data Problem Reporting System: http://wdprs.internic.net/
>>> Last update of WHOIS database: 2020-08-28T06:34:34-0700 <<<

For more information on WHOIS status codes, please visit:
  https://www.icann.org/resources/pages/epp-status-codes

If you wish to contact this domain’s Registrant, Administrative, or Technical
contact, and such email address is not visible above, you may do so via our web
form, pursuant to ICANN’s Temporary Specification. To verify that you are not a
robot, please enter your email address to receive a link to a page that
facilitates email communication with the relevant contact(s).

Web-based WHOIS:
  https://domains.markmonitor.com/whois

If you have a legitimate interest in viewing the non-public WHOIS details, send
your request and the reasons for your request to whoisrequest@markmonitor.com
and specify the domain name in the subject line. We will review that request and
may ask for supporting documentation and explanation.

The data in MarkMonitor’s WHOIS database is provided for information purposes,
and to assist persons in obtaining information about or related to a domain
name’s registration record. While MarkMonitor believes the data to be accurate,
the data is provided "as is" with no guarantee or warranties regarding its
accuracy.

By submitting a WHOIS query, you agree that you will use this data only for
lawful purposes and that, under no circumstances will you use this data to:
  (1) allow, enable, or otherwise support the transmission by email, telephone,
or facsimile of mass, unsolicited, commercial advertising, or spam; or
  (2) enable high volume, automated, or electronic processes that send queries,
data, or email to MarkMonitor (or its systems) or the domain name contacts (or
its systems).

MarkMonitor reserves the right to modify these terms at any time.

By submitting this query, you agree to abide by this policy.

MarkMonitor Domain Management(TM)
Protecting companies and consumers in a digital world.

Visit MarkMonitor at https://www.markmonitor.com
Contact us at +1.8007459229
In Europe, at +44.02032062220
--

Again wasting space

   URL of the ICANN Whois Inaccuracy Complaint Form: https://www.icann.org/wicf/
>>> Last update of whois database: 2020-08-28T13:37:01Z <<<

For more information on Whois status codes, please visit https://icann.org/epp

NOTICE: The expiration date displayed in this record is the date the
registrar's sponsorship of the domain name registration in the registry is
currently set to expire. This date does not necessarily reflect the expiration
date of the domain name registrant's agreement with the sponsoring
registrar.  Users may consult the sponsoring registrar's Whois database to
view the registrar's reported date of expiration for this registration.

Domain Name: google.com
Registry Domain ID: 2138514_DOMAIN_COM-VRSN
Registrar WHOIS Server: whois.markmonitor.com
Registrar URL: http://www.markmonitor.com
Updated Date: 2019-09-09T08:39:04-0700
Creation Date: 1997-09-15T00:00:00-0700
Registrar Registration Expiration Date: 2028-09-13T00:00:00-0700
Registrar: MarkMonitor, Inc.
Registrar IANA ID: 292
Registrar Abuse Contact Email: abusecomplaints@markmonitor.com
Registrar Abuse Contact Phone: +1.2083895770
Domain Status: clientUpdateProhibited (https://www.icann.org/epp#clientUpdateProhibited)
Domain Status: clientTransferProhibited (https://www.icann.org/epp#clientTransferProhibited)
Domain Status: clientDeleteProhibited (https://www.icann.org/epp#clientDeleteProhibited)
Domain Status: serverUpdateProhibited (https://www.icann.org/epp#serverUpdateProhibited)
Domain Status: serverTransferProhibited (https://www.icann.org/epp#serverTransferProhibited)
Domain Status: serverDeleteProhibited (https://www.icann.org/epp#serverDeleteProhibited)
Registrant Organization: Google LLC
Registrant State/Province: CA
Registrant Country: US
Registrant Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Admin Organization: Google LLC
Admin State/Province: CA
Admin Country: US
Admin Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Tech Organization: Google LLC
Tech State/Province: CA
Tech Country: US
Tech Email: Select Request Email Form at https://domains.markmonitor.com/whois/google.com
Name Server: ns4.google.com
Name Server: ns3.google.com
Name Server: ns1.google.com
Name Server: ns2.google.com
DNSSEC: unsigned
URL of the ICANN WHOIS Data Problem Reporting System: http://wdprs.internic.net/
>>> Last update of WHOIS database: 2020-08-28T06:34:34-0700 <<<

For more information on WHOIS status codes, please visit:
  https://www.icann.org/resources/pages/epp-status-codes

If you wish to contact this domain’s Registrant, Administrative, or Technical
contact, and such email address is not visible above, you may do so via our web
form, pursuant to ICANN’s Temporary Specification. To verify that you are not a
robot, please enter your email address to receive a link to a page that
facilitates email communication with the relevant contact(s).

Web-based WHOIS:
  https://domains.markmonitor.com/whois

If you have a legitimate interest in viewing the non-public WHOIS details, send
your request and the reasons for your request to whoisrequest@markmonitor.com
and specify the domain name in the subject line. We will review that request and
may ask for supporting documentation and explanation.

The data in MarkMonitor’s WHOIS database is provided for information purposes,
and to assist persons in obtaining information about or related to a domain
name’s registration record. While MarkMonitor believes the data to be accurate,
the data is provided "as is" with no guarantee or warranties regarding its
accuracy.

By submitting a WHOIS query, you agree that you will use this data only for
lawful purposes and that, under no circumstances will you use this data to:
  (1) allow, enable, or otherwise support the transmission by email, telephone,
or facsimile of mass, unsolicited, commercial advertising, or spam; or
  (2) enable high volume, automated, or electronic processes that send queries,
data, or email to MarkMonitor (or its systems) or the domain name contacts (or
its systems).

MarkMonitor reserves the right to modify these terms at any time.

By submitting this query, you agree to abide by this policy.

MarkMonitor Domain Management(TM)
Protecting companies and consumers in a digital world.

Visit MarkMonitor at https://www.markmonitor.com
Contact us at +1.8007459229
In Europe, at +44.02032062220
--

---

To optimize this you should extract the data, and add them to individual columns like:

Registry Domain ID	Registrar WHOIS Server	Registrar URL	Updated Date	Creation Date	Registry Expiry Date	Registrar	Registrar IANA ID	Registrar Abuse Contact Email	Registrar Abuse Contact Phone	Domain Status	Name Server	DNSSEC
Registry Domain ID	Currently server, but replicate = own DB and link by key_id	key_ID as this should have it's own DB	No Comment	No comment	done by expiration_date	replicate = own DB	replicate = own DB	replicate = own DB	replicate = own DB	replicate = own DB	replicate = own DB¹	bolean as 0 or 1

The same goes on for the follow fields as they are most likely to be doubles in our situations and it would enhance the way of searching/extracting data

Registrant Organization
Registrant State/Province
Registrant Country
Registrant Email: Typically "Redacted for ""Privacy"""
Admin Organization
Admin State/Province
Admin Country
Admin Email
Tech Organization
Tech State/Province
Tech Country
Tech Email

¹) Name Server should then also be cross-refs with domains, this will open up for extracting data of who uses Name Server x and if these differs from the db_name.WHOIS.Name Server then update WHOIS records. this will minimize the requests to any whois server

[spirillen]

We need to improve the way we scan/test a file thinking Any ideas @spirillen @AnonymousPoster ? I'm out of direct idea right now thinking

https://travis-ci.com/github/dns-test/porn.host.rpz is an example of the whole process which may take days thinking

The only thing I can come up with, is that the pyfunceble_status.tested is the key that is worked with in combination with the pyfunceble_status.tested_at. But this will only work with the SQL.

I also posted a few ideas here https://www.mypdns.org/T1250#23313 for how to optimize the database structure to accomplice this, but again, that is only the SQL edition, not the JSON. And to add even more evil, then we have

• DNS respond to outdated domains: https://www.mypdns.org/T1271
• PyFunceble if any test value = active Set result equal ACTIVE: https://www.mypdns.org/T1270

Which both will be the pain for testing.

Here the #115 comes to play, if we starting by reading in everything from any given source, then we can put up several slaves to test the next batch of records, but again, requires central SQL.

Conclusion
I don't see any easy way out of this, unless there is a SQL that the slaves can connect to as JSON sucks for multi user purposes and it will be a hell to setup Travis to be a central thing that at any given time should be accessible from any other Travis client, is probably impossible do the the firewall structure to protect individual projects.

The way out of this is centralizing in any way(for the sake of speed) and the flip the URI/Domains into the keys for testing, and leave the source as something that is solely added to the pyfunceble_status.tested for later extraction of tested results data for a given source.

source_tested=`-(u)f`
SELECT *
FROM PyFunceble.pyfunceble_status
	where file_id LIKE '${source_tested}'
	AND test_completed = '1';

[spirillen]

I'll bet this comment #343 (comment) would like to see the PyFunceble WHOIS DB to be published #259
I'm open for help sharing the data over my domain (server) to spread the load among several machine like an actual cluster with one master and a lot of slaves <-- He did not just say yes??? yes I did, as that what it have been called since I started writing my first lines of code to a Commodore 64...