Print Page - [project] api.loyce.club: crowdsourcing Bitcointalk data

Title: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on June 23, 2019, 03:08:04 PM

In [BRAINSTORM] Any requests for custom extensions/user scripts for BitcoinTalk? (https://bitcointalk.org/index.php?topic=5156357.0), the suggestion for a Merit data API (https://bitcointalk.org/index.php?topic=5156357.msg51540418#msg51540418) came up. User CryptoNeed (https://bitcointalk.org/index.php?action=profile;u=1072936) made a PHP script (https://bitcointalk.org/index.php?topic=5156357.msg51560197#msg51560197), and to stop going off-topic there, I've created this topic.

api.loyce.club
I wouldn't know how to build an API, and have no experience with MySQL or PHP to store data. But I've collected a lot of data (https://bitcointalk.org/index.php?topic=5145594.0) on Merit, Trust and bans (Nuke or Autoban) and wouldn't mind putting that to good use.

Join?
If anyone is interested in crowdsourcing additions, I'll be happy to host them. I would like it if any code contributions can be as free as possible, allowing anyone to build upon it and improve it. I won't be the best judge of all code, so I appreciate input on what's safe, efficient and worth using.

No spam
All my threads are now self-moderated to stop signature spam. I will remove all irrelevant posts. If you quote the entire OP, your entry will be deleted.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on June 23, 2019, 03:08:11 PM

Currently, I use http://api.loyce.club/crowdsourcing/ for testing. CryptoNeed's code (https://bitcointalk.org/index.php?topic=5156357.msg51560197#msg51560197) is there, but I'm not sure how to use it.

I would expect this to work, but it doesn't:
http://api.loyce.club/crowdsourcing/meritjson.php?donor=459836

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 23, 2019, 03:27:36 PM

Quote from: LoyceV on June 23, 2019, 03:08:11 PM

Not quite sure as it runs on my local servers without problems.
I noticed that the URL actually is http://api.loyce.club/crowdsourcing/merit.all.txt. Try to change the $url variable in the script. ($url = "merit.all.txt"; should be enough because script and source data is stored in the same directory).

I also want to credit Initscri (https://bitcointalk.org/index.php?topic=5156357.msg51566242#msg51566242). He is going to work on a combined version of this script.
Maybe we should add some error handling aswell. ;D

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: Initscri on June 25, 2019, 04:34:24 PM

Quote from: LoyceV on June 23, 2019, 03:08:11 PM

You have an error_log, feel free to shoot me a PM with the most recent error if you wish. I can take a look.

Try using the modified code I suggested in the other thread. I'm assuming you're running that as a linux server, so the code modification may be required.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: TryNinja on June 25, 2019, 06:57:56 PM

^ in that case, he will also need to log every merit transaction in the mysql database. It’s possible to make an API without an sql server.

Idk if Loyce knows mysql. But he already doesn’t know PHP, so that would just complicate his life. :D

I’ll give a shot on making the API.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 01:16:04 AM

Quote from: TryNinja on June 25, 2019, 06:57:56 PM

^ in that case, he will also need to log every merit transaction in the mysql database. It’s possible to make an API without an sql server.
Idk if Loyce knows mysql. But he already doesn’t know PHP, so that would just complicate his life. :D
I’ll give a shot on making the API.

As far as I know he doesnt know SQL. I think he is putting all the data together file by hand.
It seems that Loyce did not have uploaded the adjusted the code as under the given URL it is responsing with the old version.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: Initscri on June 26, 2019, 05:09:39 AM

Quote from: ETFbitcoin on June 25, 2019, 06:09:02 PM

Have you install or/and configure MySQL? It's definitely required to build API services.

If we need simplicity, we could do it with only a table (which shown below).

Code:

CREATE TABLE MERIT (
    ID INT(11) PRIMARY KEY NOT NULL,
    QTY INT(2) NOT NULL,
    MSG VARCHAR(25) NOT NULL,
    SENDER INT(8) NOT NULL,
    RECEIVER INT(8) NOT NULL
);

Short demo (with MariaDB, but should be fully compatible with MySQL) :

Code:

MariaDB [test]> CREATE TABLE MERIT (
    ->     ID INT(11) PRIMARY KEY NOT NULL,
    ->     QTY INT(2) NOT NULL,
    ->     MSG VARCHAR(25) NOT NULL,
    ->     SENDER INT(8) NOT NULL,
    ->     RECEIVER INT(8) NOT NULL
    -> );
Query OK, 0 rows affected (0.029 sec)

MariaDB [test]> DESC MERIT;
+----------+-------------+------+-----+---------+-------+
| Field    | Type        | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| ID       | int(11)     | NO   | PRI | NULL    |       |
| QTY      | int(2)      | NO   |     | NULL    |       |
| MSG      | varchar(25) | NO   |     | NULL    |       |
| SENDER   | int(8)      | NO   |     | NULL    |       |
| RECEIVER | int(8)      | NO   |     | NULL    |       |
+----------+-------------+------+-----+---------+-------+
5 rows in set (0.001 sec)

MariaDB [test]> SELECT * FROM MERIT;
Empty set (0.000 sec)

MariaDB [test]> INSERT INTO MERIT VALUES (1561085310, 2, "178336.msg51543068", 452769, 239406);
Query OK, 1 row affected (0.008 sec)

MariaDB [test]> SELECT * FROM MERIT;
+------------+-----+--------------------+--------+----------+
| ID         | QTY | MSG                | SENDER | RECEIVER |
+------------+-----+--------------------+--------+----------+
| 1561085310 |   2 | 178336.msg51543068 | 452769 |   239406 |
+------------+-----+--------------------+--------+----------+
1 row in set (0.000 sec)

P.S. I learn SQL a bit, but i've no idea about MySQL security or performance.

It's not required to build the API service + the script CryptoNeed built simply just runs off the system Loyce has already built.

Quote

I think he is putting all the data together file by hand.

I'm assuming he uses a script to build it.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 05:18:59 AM

Quote from: Initscri on June 26, 2019, 05:09:39 AM

I'm assuming he uses a script to build it.

Yes for sure. At least I hope so. ;D
By hand I meant sth like fetching data from the website via script.

I pm'd him an "updated" version to have forms at the html one, so that the loaded script doesn't appear totally empty.
How is your progress on the combined script with RegEx, Initscri?

Code:

<html>
	<head>
		<title>Merit LookUp</title>
	</head>
	<body>
		<form action="merithtml.php" method="get">
			Donor: <input type="text" name="donor" default="0"><input type="submit"><br />
		</form>
		<form action="merithtml.php" method="get">
			Recipient: <input type="text" name="recipient"><input type="submit"><br />	
		</form>
<?php
//GET Request by: merithtml.php?donor=USERID

$url = "merit.all.txt"; 
$file = file($url);
$file = str_replace("	", ",",$file);
$file = str_replace("\r\n", "",$file);
// put the array together
foreach ($file as $line => $value) {
	$value = explode(",", $value);
    $bigArray[] = array("Donor" => $value[3], "Recipient" => $value[4], "Merits" => $value[1], "Msg" => $value[2], "DTG" => $value[0]);
}

function searchForDonor($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Donor"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
					<td>-(".$val["Merits"].")-></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

function searchForRecipient($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Recipient"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
					<td><-(".$val["Merits"].")-</td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

if(isset($_GET["donor"])) {
	searchForDonor($_GET["donor"], $bigArray);
} 

if(isset($_GET["recipient"])) {
	searchForRecipient($_GET["recipient"], $bigArray);

}

?>

	</body>
</html>

Also, I decided to regain some of my PHP skills. Have forgotten how much fun it is. :)

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: Initscri on June 26, 2019, 06:03:03 AM

Quote from: CryptoNeed on June 26, 2019, 05:18:59 AM

Quote from: Initscri on June 26, 2019, 05:09:39 AM

I'm assuming he uses a script to build it.

Code:

<html>
	<head>
		<title>Merit LookUp</title>
	</head>
	<body>
		<form action="merithtml.php" method="get">
			Donor: <input type="text" name="donor" default="0"><input type="submit"><br />
		</form>
		<form action="merithtml.php" method="get">
			Recipient: <input type="text" name="recipient"><input type="submit"><br />	
		</form>
<?php
//GET Request by: merithtml.php?donor=USERID

$url = "merit.all.txt"; 
$file = file($url);
$file = str_replace("	", ",",$file);
$file = str_replace("\r\n", "",$file);
// put the array together
foreach ($file as $line => $value) {
	$value = explode(",", $value);
    $bigArray[] = array("Donor" => $value[3], "Recipient" => $value[4], "Merits" => $value[1], "Msg" => $value[2], "DTG" => $value[0]);
}

function searchForDonor($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Donor"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
					<td>-(".$val["Merits"].")-></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

function searchForRecipient($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Recipient"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
					<td><-(".$val["Merits"].")-</td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

if(isset($_GET["donor"])) {
	searchForDonor($_GET["donor"], $bigArray);
} 

if(isset($_GET["recipient"])) {
	searchForRecipient($_GET["recipient"], $bigArray);

}

?>

	</body>
</html>

Also, I decided to regain some of my PHP skills. Have forgotten how much fun it is. :)

Haven't had a chance to make any modifications as of yet. Been swamped.

Is that the new code you're working off of?

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 06:11:50 AM

Quote from: Initscri on June 26, 2019, 06:03:03 AM

Quote from: CryptoNeed on June 26, 2019, 05:18:59 AM

Quote from: Initscri on June 26, 2019, 05:09:39 AM

I'm assuming he uses a script to build it.

Code:

<html>
	<head>
		<title>Merit LookUp</title>
	</head>
	<body>
		<form action="merithtml.php" method="get">
			Donor: <input type="text" name="donor" default="0"><input type="submit"><br />
		</form>
		<form action="merithtml.php" method="get">
			Recipient: <input type="text" name="recipient"><input type="submit"><br />	
		</form>
<?php
//GET Request by: merithtml.php?donor=USERID

$url = "merit.all.txt"; 
$file = file($url);
$file = str_replace("	", ",",$file);
$file = str_replace("\r\n", "",$file);
// put the array together
foreach ($file as $line => $value) {
	$value = explode(",", $value);
    $bigArray[] = array("Donor" => $value[3], "Recipient" => $value[4], "Merits" => $value[1], "Msg" => $value[2], "DTG" => $value[0]);
}

function searchForDonor($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Donor"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
					<td>-(".$val["Merits"].")-></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

function searchForRecipient($uid, $array) {
	echo "<table>";
	foreach ($array as $key => $val) {
		if($val["Recipient"] === $uid) {
			//print_r($array[$key]);
			echo "<tr>
					<td>".date("d.m.Y H:i:s", $val["DTG"])."</td>
					<td><a href='https://bitcointalk.org/index.php?topic=".$val["Msg"]."#". strstr($val["Msg"], "msg")."'>".$val["Msg"]."</a></td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Recipient"]."'>".$val["Recipient"]."</a></td>
					<td><-(".$val["Merits"].")-</td>
					<td><a href='https://bitcointalk.org/index.php?action=profile;u=".$val["Donor"]."'>".$val["Donor"]."</a></td>
				</tr>";
		} 
	}
	echo "</table>";
	return null;
}

if(isset($_GET["donor"])) {
	searchForDonor($_GET["donor"], $bigArray);
} 

if(isset($_GET["recipient"])) {
	searchForRecipient($_GET["recipient"], $bigArray);

}

?>

	</body>
</html>

Also, I decided to regain some of my PHP skills. Have forgotten how much fun it is. :)

Haven't had a chance to make any modifications as of yet. Been swamped.

Is that the new code you're working off of?

Nah, currently not working on it as I have some social events coming up this week. I just added a form and thats all.
Will have some time next week.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on June 26, 2019, 09:03:08 AM

Quote from: CryptoNeed on June 23, 2019, 03:27:36 PM

It was:

Code:

CryptoNeed.html:$url = "merit.all.txt"; 
CryptoNeed.php:$url = "http://loyce.club/Merit/merit.all.txt";
CryptoNeed2.php:$url = "http://loyce.club/Merit/merit.all.txt";

That location still exists. I've implemented your suggested changes, but the error is the same.

Quote from: Initscri on June 25, 2019, 04:34:24 PM

You have an error_log, feel free to shoot me a PM with the most recent error if you wish. I can take a look.

I've renamed it to error_log.txt (http://api.loyce.club/crowdsourcing/error_log.txt), so you can click it.

Quote from: ETFbitcoin on June 25, 2019, 06:09:02 PM

Have you install or/and configure MySQL? It's definitely required to build API services.

My shared hosting:

Code:

Apache Version 	2.4.39
PHP Version 	5.6.40
MySQL Version 	5.7.26-cll-lve
Architecture 	x86_64
Operating System 	linux

Quote from: TryNinja on June 25, 2019, 06:57:56 PM

Idk if Loyce knows mysql. But he already doesn’t know PHP, so that would just complicate his life. :D

I don't know either one :(

Quote

I’ll give a shot on making the API.

Thanks :)

Quote from: CryptoNeed on June 26, 2019, 01:16:04 AM

As far as I know he doesnt know SQL.

Correct.

Quote

I think he is putting all the data together file by hand.

Incorrect (https://bitcointalk.org/index.php?topic=4826180.msg50403802#msg50403802) :P (I update 350,000 files per week now)

Quote from: CryptoNeed on June 26, 2019, 01:16:04 AM

It seems that Loyce did not have uploaded the adjusted the code as under the given URL it is responsing with the old version.

I postponed it until I had a bit more time. I'll try the suggestions now.

Quote from: CryptoNeed on June 26, 2019, 05:18:59 AM

I pm'd him an "updated" version to have forms at the html one, so that the loaded script doesn't appear totally empty.

I've uploaded it to ~~http://api.loyce.club/crowdsourcing/CryptoNeed.html but it gives some errors.~~ http://api.loyce.club/crowdsourcing/CryptoNeed3.php.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 09:19:54 AM

Quote from: LoyceV on June 26, 2019, 09:03:08 AM

I've uploaded it to http://api.loyce.club/crowdsourcing/CryptoNeed.html but it gives some errors.

I pm'd you. File extension should be .php instead of .html.
(I called it html version because of it's output.)

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: Initscri on June 26, 2019, 03:46:28 PM

Quote from: LoyceV on June 26, 2019, 09:03:08 AM

Quote from: CryptoNeed on June 23, 2019, 03:27:36 PM

It was:

Code:

CryptoNeed.html:$url = "merit.all.txt"; 
CryptoNeed.php:$url = "http://loyce.club/Merit/merit.all.txt";
CryptoNeed2.php:$url = "http://loyce.club/Merit/merit.all.txt";

That location still exists. I've implemented your suggested changes, but the error is the same.

Quote from: Initscri on June 25, 2019, 04:34:24 PM

You have an error_log, feel free to shoot me a PM with the most recent error if you wish. I can take a look.

I've renamed it to error_log.txt (http://api.loyce.club/crowdsourcing/error_log.txt), so you can click it.

Quote from: ETFbitcoin on June 25, 2019, 06:09:02 PM

Have you install or/and configure MySQL? It's definitely required to build API services.

My shared hosting:

Code:

Apache Version 	2.4.39
PHP Version 	5.6.40
MySQL Version 	5.7.26-cll-lve
Architecture 	x86_64
Operating System 	linux

Quote from: TryNinja on June 25, 2019, 06:57:56 PM

Idk if Loyce knows mysql. But he already doesn’t know PHP, so that would just complicate his life. :D

I don't know either one :(

Quote

I’ll give a shot on making the API.

Thanks :)

Quote from: CryptoNeed on June 26, 2019, 01:16:04 AM

As far as I know he doesnt know SQL.

Correct.

Quote

I think he is putting all the data together file by hand.

Incorrect (https://bitcointalk.org/index.php?topic=4826180.msg50403802#msg50403802) :P (I update 350,000 files per week now)

Quote from: CryptoNeed on June 26, 2019, 01:16:04 AM

It seems that Loyce did not have uploaded the adjusted the code as under the given URL it is responsing with the old version.

I postponed it until I had a bit more time. I'll try the suggestions now.

Quote from: CryptoNeed on June 26, 2019, 05:18:59 AM

I pm'd him an "updated" version to have forms at the html one, so that the loaded script doesn't appear totally empty.

I've uploaded it to ~~http://api.loyce.club/crowdsourcing/CryptoNeed.html but it gives some errors.~~ http://api.loyce.club/crowdsourcing/CryptoNeed3.php.

You may need to implement my changes here: https://bitcointalk.org/index.php?topic=5156357.msg51564286#msg51564286

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on June 26, 2019, 05:06:33 PM

Quote from: Initscri on June 26, 2019, 03:46:28 PM

You may need to implement my changes here: https://bitcointalk.org/index.php?topic=5156357.msg51564286#msg51564286

Results:
This works: http://api.loyce.club/crowdsourcing/CryptoNeed_Initscri_tab.php?donor=35
This doesn't work: http://api.loyce.club/crowdsourcing/CryptoNeed_Initscri_tab.php?recipient=35

The one that works is quite slow to generate, I'm not sure how useful this is going to be for an API that loads data on many users.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 06:07:55 PM

Quote from: LoyceV on June 26, 2019, 05:06:33 PM

Quote from: Initscri on June 26, 2019, 03:46:28 PM

You may need to implement my changes here: https://bitcointalk.org/index.php?topic=5156357.msg51564286#msg51564286

Can't follow all the changes anymore, but at least one versions seems to be working. Can you paste the code of the file here again?

It's so slow because of the .txt file. And the larger the .txt file becomes, the longer it will need to load.
The Algo is as follows:
1. Open/Download the file
2. Read the file (line by line)
3. Delete special chars
4. Build an array
5. Go through the whole array
6. Display matches with user id.

What we can do against this is:
Write a script to to import the whole txt into a SQL-DB. This would increase the speed to "infinity". But once the DB is established you need to keep care of it and save all new data to this DB.
I am not sure how you process all the data. Maybe you can tell us how exactly you get the data and how you are saving it into your .txt file.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on June 26, 2019, 07:17:15 PM

Quote from: CryptoNeed on June 26, 2019, 06:07:55 PM

Can't follow all the changes anymore, but at least one versions seems to be working. Can you paste the code of the file here again?

I've copied each .php to .txt at http://api.loyce.club/crowdsourcing/ so you can read them.

Quote

I am not sure how you process all the data. Maybe you can tell us how exactly you get the data and how you are saving it into your .txt file.

This one is actually quite simple:
1. I download and extract https://bitcointalk.org/merit.txt.xz
2. I overwrite the last ~113 days in merit.all.txt with the new ~120 days
The format is the same as the forum's data dump, I only add historic records.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: CryptoNeed on June 26, 2019, 07:58:41 PM

Quote from: LoyceV on June 26, 2019, 07:17:15 PM

Quote from: CryptoNeed on June 26, 2019, 06:07:55 PM

Can't follow all the changes anymore, but at least one versions seems to be working. Can you paste the code of the file here again?

I've copied each .php to .txt at http://api.loyce.club/crowdsourcing/ so you can read them.

I will have a look deeper look into it when I have some time.

Quote from: LoyceV on June 26, 2019, 07:17:15 PM

Quote

I am not sure how you process all the data. Maybe you can tell us how exactly you get the data and how you are saving it into your .txt file.

Ahh okay.
You know what? I skip the deeper look into the script. As soon as I am back home, I will provide you with:
1. a new script which creates a SQL DB with a table.
2. some slim browser interface to paste the effected lines from the .txt-file into.
3. some SQL magic :D

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: Initscri on June 27, 2019, 04:29:56 AM

Quote from: LoyceV on June 26, 2019, 05:06:33 PM

Quote from: Initscri on June 26, 2019, 03:46:28 PM

You may need to implement my changes here: https://bitcointalk.org/index.php?topic=5156357.msg51564286#msg51564286

You can probably speed it up by caching the parsed $bigArray variable in a cache file.

I can write this into my code when I work on it.
It may not be by much, but it will drop a step.

@Crypto, can you send me the current code you have now?

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on November 14, 2019, 12:00:53 PM

I'll take the discussion from another topic here:

Quote from: lulucrypto on November 13, 2019, 08:58:52 PM

I like the idea ! :D

But like to automate things, I think it would not be very complicated to automate the process with a bit of PHP code for example ;)

If it interests you, I would be ready to help you for that !

Quote from: lulucrypto on November 13, 2019, 09:50:20 PM

Oh, okay, You are self-hosting maybe ? If so which OS ( Raspberry, Other ? ) ?

The data is created on my old netbook, then uploaded to my shared hosting:

Code:

Apache Version 	2.4.41
PHP Version 	7.2.23
MySQL Version 	5.7.28-cll-lve
Architecture 	x86_64
Operating System 	linux

Let's continue in this topic :)

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: lulucrypto on November 14, 2019, 10:00:00 PM

Okay, so if I understand correctly, you still have a VPS ( Shared hosting ) behind the domain "api.loyce.club" ?

So, I do not understand too much the story with the Netbook ^^

Why not do everything on the VPS at once ?

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on November 14, 2019, 10:20:01 PM

Quote from: lulucrypto on November 14, 2019, 10:00:00 PM

Okay, so if I understand correctly, you still have a VPS ( Shared hosting ) behind the domain "api.loyce.club" ?

Correct.

Quote

So, I do not understand too much the story with the Netbook ^^

I use the netbook to process and upload data.

Quote

Why not do everything on the VPS at once ?

I tried, but my (cheap) VPS turned out to be unreliable, so this "temporary" solution was meant to get stuff working again.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: lulucrypto on November 14, 2019, 10:28:22 PM

Oh, okay, but you do not want to upgrade to a better VPS ( You can't maybe ? ) ?

Otherwise, I just looked to recover the trust's, but there is something I did not know, the trust's is only displayed for connected members. I am wrong ? ???

If that's right, if I use a basic account ( Without any rank ) will I see all the trust's ?

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on November 14, 2019, 10:49:04 PM

Quote from: lulucrypto on November 14, 2019, 10:28:22 PM

Oh, okay, but you do not want to upgrade to a better VPS ( You can't maybe ? ) ?

I've been postponing it due to bad experiences and lack of time. Although I did test a few, and one of them actually performs good for it's price, but it's very low on RAM and no SSD, so it won't work for my large data collection.
I'm in no rush, as it currently works. Meanwhile, most of my tasks are scheduled on the netbook without needing my attention, so in the future I can move the whole thing to a VPS at once.

Quote

Otherwise, I just looked to recover the trust's, but there is something I did not know, the trust's is only displayed for connected members. I am wrong ? ???

Correct.

Quote

If that's right, if I use a basic account ( Without any rank ) will I see all the trust's ?

Correct.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: lulucrypto on November 15, 2019, 12:00:17 AM

Okay, I understand ^^

So the principle of the project, it will be to have at first, an API that will take care of recovering the details of trust's, which will be sent in JSON format.

Once the API ready and functional, I will develop a small page in PHP that will handle with the help of the API, to return an image corresponding to the trust's details from profile ID provided in the URL.

And to do that correctly, the API and the PHP page will be open source on Github :)

So I start this tomorrow, and come back to you as soon as I have news ! ;)

Okay so,

The first version of API is ready to test :)

I have create a dedicated topic for the project here :
- https://bitcointalk.org/index.php?topic=5201993.msg53092166#msg53092166

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: PrimeNumber7 on November 18, 2019, 06:14:57 AM

I created a script to get your merit data into a sqllite file, which IMO is best for your situation. I also created an API to query merit data.

Both files are written in python. To run the files, you navigate to the folder they are located in and in the command prompt type python [file name]; the file name should end in .py. You will need to run the first file first. Neither file will cause your SQLlite database to update automatically, but if the API is deployed, I could easily write a script to update the SQLlite database with new merit transactions.

When you run the file that gets data into the SQLlite file, it will create a SQLlite file. This file may also take a minute or so to run.

The API file should be run second ideally. It will need to continue running as long as you want the API to run.

As a standard disclaimer: I am not aware of any security vulnerabilities with my code, but you should implement my code at your own risk.

When merit transactions are being queried, if there are none, a single dummy transaction with 0 merit is returned.

If my code is implemented, and there is demand, I can update the API to return the usernames associated with the UIDs in question.

My API file is not very well commented. I don't have very much free time to spend on coding side projects, and sat down a number of times to work on this code. If you need better comments, I can create better/more comments.

My script to get your merit data into a SQLlite file is below:

Code:

#dependencies
import pandas as pd
import numpy as np
#from datetime import datetime #this library is not required
from sqlalchemy import create_engine, Column, Integer, String, Float
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base

#path of sqlite file goes here in relation to current file
database_path = 'merit.sqlite'

#create DataFrame from mert transaction history file on loyce.club
data = pd.read_csv('http://loyce.club/Merit/merit.all.txt', sep='	', header=None)
#rename columns
data = data.rename(columns={0: 'time', 1: 'number_of_merit', 2:'message_id', 3:'UID_from', 4: 'UID_to'})



#connect to sqllite db
engine = create_engine(f"sqlite:///{database_path}")
session = Session(engine)



#use default declarative base function as variable 'Base'
Base = declarative_base()

#define table schema
class Merit(Base):
    __tablename__ = 'merit'
    id = Column(Integer, primary_key=True)#this is a column with a unique value for each transaction
    time = Column(Integer) #unix time
    number_of_merit = Column(Integer)
    message_id = Column(String(25))
    uid_from = Column(Integer)
    uid_to = Column(Integer)


#create table in sqllite file
Base.metadata.create_all(engine)


#set first value for id as 1
id1 = 1
#loop through DataFrame to add each row in the DataFrame to the SQLlite DB. 
for x in np.arange(len(data)):
    session.add(Merit(id=id1, time=int(data['time'][x]), number_of_merit=int(data['number_of_merit'][x]), 
                      message_id=data['message_id'][x], uid_from=int(data['UID_from'][x]), uid_to=int(data['UID_to'][x])))
    id1 = id1 + 1 #after the row is added, the id1 variable value will be increased by one
    #will commit rows in batches of 100
    if len(session.new) > 100:
        session.commit()
session.commit() #commit last batch of rows


#check to make sure all rows were successfully imported
if len(session.query(Merit.id).all()) == len(data):
    print(f'All the data from the DataFrame was successfully imported into a SQL file found at {database_path}')
else:
    print(f'There was a problem importing all the merit transactions and {len(data) - len(session.query(Merit.id).all())} were not imported. Troubleshooting is required')

The code for the API is below:

Code:

#dependencies
import pandas as pd
import numpy as np
from datetime import datetime
from sqlalchemy import create_engine, Column, Integer, String, Float, func
from sqlalchemy.orm import Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.automap import automap_base
from flask import Flask, jsonify


#path of sqlite file goes here in relation to current file
database_path = 'merit.sqlite'

#connect to sqllite db
engine = create_engine(f"sqlite:///{database_path}")



#reflect an existing database into a new model
Base = automap_base()
# reflect the tables
Base.prepare(engine, reflect=True)

# Save reference to the table
Merit = Base.classes.merit


#set variable 'app' to run Flask
app = Flask(__name__)


@app.route("/")
def welcome():
    description1 = '''
    Available Routes:<br/>
    /api/v0.1/number_received/[uid]</br>
    /api/v0.1/number_sent/[uid]</br>
    /api/v0.1/between/[uid_from]/[uid_to]</br>
    /api/v0.1/received/[uid]</br>
    /api/v0.1/sent/[uid]</br>
    </br></br>
    /api/v0.1/number_received/[uid]</br>
    replace [uid] with uid of forum member, must be an integer</br>
    returns:</br> Total Received Merit:  the total merit the uid has received</br>
    uid: the uid you are querying</br></br>
    /api/v0.1/number_sent/[uid]</br>
    replace [uid] with uid of forum member, must be an integer</br>
    returns:</br> Total Sent Merit:  the total merit the uid has sent</br>
    uid: the uid you are querying</br></br>
    /api/v0.1/between/[uid_from]/[uid_to]</br>
    replace [uid_from] and [uid_to] with uids of forum members, must be  integers</br>
    Queries merit transactions from [uid_from] to [uid_to]</br>
    returns:</br>Total Received Merit: the total merit sent to [uid_to] from [uid_from]</br>
    Sent from: [uid_from]</br>
    Sent to: [uid_to]</br>
    Transactions: List of dictionaries of merit transactions that contain the following information:</br>
    time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
    Month: Month of merit transaction</br>
    Day of Week: the day of the week of the merit transaction
    number of merit: the number of merit that was sent from [uid_from] to [uid_to] in the merit transaction</br>
    Post: Post ID that received merit </br></br>
    /api/v0.1/received/[uid]</br>
    replace [uid] with uid of forum member, must be an integer</br>
    returns:<br/>
    Total Received Merit:  the total merit the uid has received</br>
    Sent to: the uid you are querying</br>
    Transactions: List of dictionaries of merit transactions that contain the following information:</br>
    time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
    Month: Month of merit transaction</br>
    Day of Week: the day of the week of the merit transaction
    number of merit: the number of merit that was sent to [uid] in the merit transaction</br>
    Post: Post ID that received merit </br>
    Sent from: the uid that sent the merit transaction</br></br>
    /api/v0.1/sent/[uid]</br>
    replace [uid] with uid of forum member, must be an integer</br>
    returns:<br/>
    Total Sent Merit:  the total merit the uid has sent</br>
    Sent to: the uid you are querying</br>
    Transactions: List of dictionaries of merit transactions that contain the following information:</br>
    time: time of merit transaction in the following format: yyyy-mm-dd hh:mm:ss</br>
    Month: Month of merit transaction</br>
    Day of Week: the day of the week of the merit transaction
    number of merit: the number of merit that was sent from [uid_from] in the merit transaction</br>
    Post: Post ID that received merit </br>
    Sent to: the uid that merit was sent to in the subject transaction</br></br>
    
    '''
    


    return (description1)



@app.route('/api/v0.1/number_received/<uid>')
def number_received(uid):
    try:
        #confirm the input was an integer
        uid1 = int(uid)
        #connect to sqllite DB
        session = Session(engine)
        response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_to == uid1).all()
        session.close()
        response = list(np.ravel(response))
        response = int(response[0])
        response_list = []
        merit_received = {}
        merit_received['Total Received Merit'] = response
        merit_received['uid'] = uid1
        response_list.append(merit_received)
        return jsonify(response_list) #jsonify
    except ValueError:
        value1 = {'Error': f'{uid} is not an Integer. Please reformat into an Integer and try again'}
        return jsonify(value1)
    except TypeError:
        no_merit = {"Total Received Merit":0, "uid":uid1}
        return jsonify(no_merit)



@app.route('/api/v0.1/number_sent/<uid>')
def number_sent(uid):
    try:
        #confirm the input was an integer
        uid1 = int(uid)
        #connect to sqllite DB
        session = Session(engine)
        response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == uid1).all()
        session.close()
        response = list(np.ravel(response))
        response = int(response[0])
        response_list = []
        merit_sent = {}
        merit_sent['Total Sent Merit'] = response
        merit_sent['uid'] = uid1
        response_list.append(merit_sent)
        return jsonify(response_list)
    except ValueError:
        value1 = {'Error': f'{uid} is not an Integer. Please reformat into an Integer and try again'}
        return jsonify(value1)
    except TypeError:
        no_merit = {"Total Sent Merit":0, "uid":uid1}
        return jsonify(no_merit)



@app.route('/api/v0.1/between/<fromm>/<to>')
def between(fromm, to):
    try:
        #confirm the input was an integer
        from1 = int(fromm)
        to1 = int(to)
        #connect to sqllite DB
        session = Session(engine)
        response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == from1).filter(Merit.uid_to == to1).all()
        response2 = session.query(Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_from == from1).filter(Merit.uid_to == to).all()
        session.close()##pick up coding here
        response = list(np.ravel(response))
        response = int(response[0])
        response_list = []
        merit_sent = {}
        merit_sent['Total Received Merit'] = response
        merit_sent['Sent from'] = from1
        merit_sent['Sent to'] = to1
        
        response3 = []
        for merit_number, message_id, time in response2:
            response2_dict = {}
            time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
            time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
            time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
            response2_dict['time'] = time1 
            response2_dict['Month'] = time_month
            response2_dict['Day of Week'] = time_day
            response2_dict['number of merit'] = merit_number
            response2_dict['Post'] = message_id
            response3.append(response2_dict)
        merit_sent['Transactions'] = response3
        response_list.append(merit_sent) 
        return jsonify(response_list)
    except ValueError:
        value1 = {'Error': f'{fromm} or {to} is not an Integer. Please reformat into an Integer and try again'}
        return jsonify(value1)
    except TypeError:
        no_merit = {"Total Received Merit":0, "Sent from":from1, 'Sent to': to1,
                    'Transactions':[{'time': '2009-01-08 08:21:00','Month': 
                                     'January','Day of Week': 'Thursday','number of merit': 0,
                                     'Post': '9999999.msg999999999'}] }
        return jsonify(no_merit)
    


@app.route('/api/v0.1/received/<to>')
def transactions_received(to):
    try:
        #confirm the input was an integer
        to1 = int(to)
        #connect to sqllite DB
        session = Session(engine)
        response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_to == to1).all()
        response2 = session.query(Merit.uid_from, Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_to == to).all()
        session.close()
        response = list(np.ravel(response))
        response = int(response[0])
        response_list = []
        merit_sent = {}
        merit_sent['Total Received Merit'] = response
        merit_sent['Sent to'] = to1
        
        response3 = []
        for received_from, merit_number, message_id, time in response2:
            response2_dict = {}
            time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
            time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
            time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
            response2_dict['time'] = time1 
            response2_dict['Month'] = time_month
            response2_dict['Day of Week'] = time_day
            response2_dict['number of merit'] = merit_number
            response2_dict['Post'] = message_id
            response2_dict['Sent from'] = received_from
            response3.append(response2_dict)
        merit_sent['Transactions'] = response3
        response_list.append(merit_sent) #this might need to be moved to the end
        return jsonify(response_list)
    except ValueError:
        value1 = {'Error': f'{to} is not an Integer. Please reformat into an Integer and try again'}
        return jsonify(value1)
    except TypeError:
        no_merit = {"Total Received Merit":0, 'Sent to': to1,
                    'Transactions':[{'time': '2009-01-08 08:21:00','Month': 
                                     'January','Day of Week': 'Thursday','number of merit': 0,
                                     'Post': '9999999.msg999999999', 'Sent from': 2}] }
        return jsonify(no_merit)
    


@app.route('/api/v0.1/sent/<fromm>')
def transactions_sent(fromm):
    try:
        #confirm the input was an integer
        from1 = int(fromm)
        #connect to sqllite DB
        session = Session(engine)
        response = session.query(func.sum(Merit.number_of_merit)).filter(Merit.uid_from == from1).all()
        response2 = session.query(Merit.uid_to, Merit.number_of_merit, Merit.message_id, Merit.time).filter(Merit.uid_from == from1).all()
        session.close()##pick up coding here
        response = list(np.ravel(response))
        response = int(response[0])
        response_list = []
        merit_sent = {}
        merit_sent['Total Sent Merit'] = response
        merit_sent['Sent from'] = from1
        
        response3 = []
        for sent_to, merit_number, message_id, time in response2:
            response2_dict = {}
            time1 = datetime.utcfromtimestamp(int(time)).strftime('%Y-%m-%d %H:%M:%S')
            time_day = datetime.utcfromtimestamp(int(time)).strftime('%A') #day of week
            time_month = datetime.utcfromtimestamp(int(time)).strftime('%B') #month
            response2_dict['time'] = time1 
            response2_dict['Month'] = time_month
            response2_dict['Day of Week'] = time_day
            response2_dict['number of merit'] = merit_number
            response2_dict['Post'] = message_id
            response2_dict['sent to'] = sent_to
            response3.append(response2_dict)
        merit_sent['Transactions'] = response3
        response_list.append(merit_sent) #this might need to be moved to the end
        return jsonify(response_list)
    except ValueError:
        value1 = {'Error': f'{fromm} is not an Integer. Please reformat into an Integer and try again'}
        return jsonify(value1)
    except TypeError:
        no_merit = {"Total Received Merit":0, "Sent from":from1, 
                    'Transactions':[{'time': '2009-01-08 08:21:00','Month': 
                                     'January','Day of Week': 'Thursday','number of merit': 0,
                                     'Post': '9999999.msg999999999', 'Sent to': 2}] }
        return jsonify(no_merit)
    


if __name__ == '__main__':
    app.run(debug=False)
   #debug needs to be set to False in production, but can be set to True in a non-public facing setting.

If the indenting did not transfer over properly, let me know and I will upload this to github. When you run the API file, you may get an error message saying not to run the code in production, but I believe it should be sufficient for your level of use.

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: lulucrypto on November 18, 2019, 04:13:30 PM

@LoyceV An important security tip for your API project :

I strongly recommend that you block access to all files except those that need to be seen.

Let me explain :

If you go to your domain "http://api.loyce.club/", it is possible to consult all the files.

Currently it is not very embarrassing ( And for my API project it will not bother me since I have already added a file to handle this ), however if one day you include a source code with sensitive information ( Login / Password ), it will block access to the file containing this information. Otherwise, it will be possible to recover this information very simply.

To block this kind of file / folder with Apache, I recommend these two links :
- Blocker a folder : https://stackoverflow.com/a/19118529
- Block a file: https://stackoverflow.com/a/11729748

Title: Re: [project] api.loyce.club: crowdsourcing Bitcointalk data
Post by: LoyceV on November 19, 2019, 07:31:39 PM

Quote from: PrimeNumber7 on November 18, 2019, 06:14:57 AM

I created a script to get your merit data into a sqllite file, which IMO is best for your situation. I also created an API to query merit data.

Thanks! It looks like you put a lot of time into this. But: I feel like I'm in way over my head :(

Quote

Both files are written in python. To run the files, you navigate to the folder they are located in and in the command prompt type python [file name]; the file name should end in .py. You will need to run the first file first.

I don't even have SSH access to my current (shared) webhosting, so I don't think I can do this.

Bitcoin Forum

Bitcoin => Project Development => Topic started by: LoyceV on June 23, 2019, 03:08:04 PM