[GSoC Task] Designing and implementing the APIs for calculating and displaying ranks of the User (all-time, monthly, weekly).

madhurgupta10 commented 4 years ago

Summary: Designing and implementing the APIs for calculating and displaying ranks of the User (all-time, monthly, weekly).

[x] Discuss and Design the API
[x] Code the API in Python and Test it using Postman.

Would you like to work on the issue? Yes

madhurgupta10 commented 4 years ago

@maskaravivek @nicolas-raoul I would like to discuss my API implementation with you.

I think we will have a GET request to fetch the leaderboard users with parameter as duration (all-time, monthly, weekly). This would return use username, ranks, uploads.

http://example.com/api?duration=monthly

[
    {
        "username": "Madhurgupta10",
        "uploads": 10,
        "rank": 1
    },
    {
        "username": "some_user_1",
        "uploads": 8,
        "rank": 2
    },
    {
        "username": "some_user_2",
        "uploads": 6,
        "rank": 3
    },
    {
        "username": "some_user_3",
        "uploads": 4,
        "rank": 4
    },
    {
        "username": "some_user_4",
        "uploads": 2,
        "rank": 5
    }
]

To add a new user to leaderboard we can have a POST and PUT methods and DELETE method for removing the user.

I would like to know, where I can get the number of uploads done by a username in a particular timeframe?

In this script the condition is hardcoded log_timestamp > "20171101000000" and log_timestamp < "20190801000000";

nicolas-raoul commented 4 years ago

About the JSON format: I think more info will be required (uploaded/nearby/used), so I guess a second parameter is needed? Like http://example.com/api?duration=monthly&game=nearby

When a user first opens the achievements screen, I would suggest using POST to register this user's interest in being in the rankings. I don't think we need a DELETE method.

To get the number of uploads you can probably adapt this SQL script and use SQL's COUNT operation. Do you know how to run SQL on Tools from an API call?

madhurgupta10 commented 4 years ago

About the JSON format: I think more info will be required (uploaded/nearby/used), so I guess a second parameter is needed? Like http://example.com/api?duration=monthly&game=nearby

Agreed!

When a user first opens the achievements screen, I would suggest using POST to register this user's interest in being in the rankings. I don't think we need a DELETE method.

Okay!

To get the number of uploads you can probably adapt this SQL script and use SQL's COUNT operation. Do you know how to run SQL on Tools from an API call?

@nicolas-raoul I know how to write SQL queries but I am not able to understand how the database is defined and is working for common misc APIs. Could you please share the resources where I can find the database schemas?

nicolas-raoul commented 4 years ago

Do you have ssh access to tools? If yes, can you start a SQL client there on command line? If yes, with SHOW and DESCRIBE you will probably be able to find out the structure and get the information you want :-)

madhurgupta10 commented 4 years ago

@nicolas-raoul I don't have access to the VM, how can I get the credentials?

nicolas-raoul commented 4 years ago

Steps 1 to 3 here I think: https://tools.wmflabs.org/admin/

madhurgupta10 commented 4 years ago

@nicolas-raoul Thanks

Is this the correct one? https://toolsadmin.wikimedia.org/tools/id/commons-android-app

I don't think I have access to this one!

nicolas-raoul commented 4 years ago

Looks like it :-)

madhurgupta10 commented 4 years ago

I have applied for the access! download (1)

maskaravivek commented 4 years ago

@madhurgupta10 Let me know when you get your membership request approved. I will add you as a maintainer in https://toolsadmin.wikimedia.org/tools/id/commons-android-app.

Also, can you check if you are able to access this repo?

https://phabricator.wikimedia.org/source/tool-commons-android-app/

madhurgupta10 commented 4 years ago

@madhurgupta10 Let me know when you get your membership request approved. I will add you as a maintainer in https://toolsadmin.wikimedia.org/tools/id/commons-android-app.

Also, can you check if you are able to access this repo?

https://phabricator.wikimedia.org/source/tool-commons-android-app/

@maskaravivek Thanks! Yes I can access this https://phabricator.wikimedia.org/source/tool-commons-android-app/

madhurgupta10 commented 4 years ago

@nicolas-raoul @maskaravivek my request for access was approved, I tried to SSH using the following ssh -J madhurgupta10@primary.bastion.wmflabs.org madhurgupta10@commons-android-app.eqiad.wmflabs but did not work.

nicolas-raoul commented 4 years ago

What documentation/procedure did you follow?

madhurgupta10 commented 4 years ago

What documentation/procedure did you follow?

https://wikitech.wikimedia.org/wiki/Help:Accessing_Cloud_VPS_instances

nicolas-raoul commented 4 years ago

Could you please detail what you checked, following the "Troubleshooting" section?

did not work

What error message do you get?

madhurgupta10 commented 4 years ago

Capture

madhurgupta10 commented 4 years ago

@nicolas-raoul @maskaravivek

This is the procedure I followed, let me know if I a doing this correctly or not.

I generated a SSH key using $ ssh-keygen -t rsa -b 4096 -C "[email]"
Added key to https://toolsadmin.wikimedia.org/profile/settings/ssh-keys/
Executed the SSH command ssh -J madhurgupta10@primary.bastion.wmflabs.org madhurgupta10@commons-android-app.eqiad.wmflabs

nicolas-raoul commented 4 years ago

This error is typical of SSH keys not being recognized.

You added the public key to toolsadmin, right?

@maskaravivek What exact commands do you use to connect?

nicolas-raoul commented 4 years ago

If you have not found a solution, please connect to IRC and ask there: https://wikitech.wikimedia.org/wiki/Help:Accessing_Cloud_VPS_instances#Communication_and_support

If noone answers on IRC, please ask on the mailing list linked from the same section, giving as many details as possible :-)

madhurgupta10 commented 4 years ago

@nicolas-raoul Thanks, I can access it now. I was missing the step ssh-add key and using the wrong hostname, the correct one is tools-login.wmflabs.org

madhurgupta10 commented 4 years ago

Edit: I think we can just use commonswiki database @nicolas-raoul @maskaravivek What is the hostname for our database?

madhurgupta10 commented 4 years ago

select count(*) from logging_userindex where log_type="upload" and log_actor=(select actor_id from actor where actor_name="Madhurgupta10") and log_timestamp > "20200608000000" and log_timestamp < "20200609000000"

I can count the uploads using the following SQL Query :)

madhurgupta10 commented 4 years ago

@nicolas-raoul @maskaravivek How can I connect my database with MySQL workbench? To write APIs I need the make an os.environ file

nicolas-raoul commented 4 years ago

Can't you use scripts such as the ones that are already in the repository?

By "MySQL workbench", do you mean the GUI tool? I don't think there is any way to connect directly, for obvious security reasons.

If you need to create a file, the ssh command line is far enough :-)

madhurgupta10 commented 4 years ago

Can't you use scripts such as the ones that are already in the repository?

I am unable to run the script, It says 'nouser'

nicolas-raoul commented 4 years ago

You are able to run the uploadsbyuser.py script from a web browser, right?

Why not copy this script and make it do what you want, for instance create tables or run SELECT queries?

madhurgupta10 commented 4 years ago

You are able to run the uploadsbyuser.py script from a web browser, right?

Correct, I am unable to run this file because

if 'QUERY_STRING' in os.environ:
    QS = os.environ['QUERY_STRING']

neither my local system nor the Cloud Instance has this QUERY_STRING

Why not copy this script and make it do what you want, for instance create tables or run SELECT queries?

That what I would like to do but I am unable to connect to the database using python script. If I run my SQL directly in the terminal it works, but not inside a python script. What I suspect it is due to some username or hostname issue.

madhurgupta10 commented 4 years ago

Capture

nicolas-raoul commented 4 years ago

Please run uploadsbyuser.py not from command line, but in a web browser. You can see the exact syntax in logcat, as the Android app calls it successfully.

madhurgupta10 commented 4 years ago

@nicolas-raoul This one works on the broswer https://tools.wmflabs.org/urbanecmbot/commonsmisc/uploadsbyuser.py?user=Martin%20Urbanec

But it is already a deployed API, I want to test my code locally first before deploying it. What I understood is that when we add a .py file to this repo, it automatically connects to server right? But how can I test my code without adding it to the repo?

maskaravivek commented 4 years ago

@madhurgupta10 The easiest way would be to create a separate branch and test it on the VM itself. I am not sure if it would be possible to set it up locally.

madhurgupta10 commented 4 years ago

@maskaravivek @nicolas-raoul The API is now live at https://tools.wmflabs.org/commons-android-app/tool-commons-android-app/leaderboard.py?user=Madhurgupta10&duration=yearly&category=nearby

I will update the docs for all the APIs I made :)

All the code is available at https://phabricator.wikimedia.org/source/tool-commons-android-app/

With your permission, we can close this issue.

commons-app / commonsmisc

[GSoC Task] Designing and implementing the APIs for calculating and displaying ranks of the User (all-time, monthly, weekly). #20