Sim City 4 Devotion Forums

SimCity 4 Devotion Welcome Portal => LEX File Exchange Discussion => LEX Development Centre => Topic started by: CasperVg on June 13, 2014, 11:32:42 AM

Title: SC4D LEX API Source-code on Github
Post by: CasperVg on June 13, 2014, 11:32:42 AM
Source code and documentation for the SC4D LEX API (a REST server for LEX functionality in mobile apps) can be found on Github now.

https://github.com/caspervg/SC4Devotion-LEX-API (https://github.com/caspervg/SC4Devotion-LEX-API)

Developers, feel free to post suggestions for features or code improvements either here or in the Github issues system.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 11, 2014, 05:08:30 PM
regarding search functionality... there is an option to search by author... but... where can I get list of all authors/creators to get their IDs...
actually I don't think that API user need to know other users IDs... for this we need only name (already unique (I assume) in db table)

so allow search by authors name and give us an option to retrieve list of authors :)

edit: lex_category, lex_type field values... I can get this from html source but it should be in documentation ;)
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 12, 2014, 02:20:48 AM
There's actually a way to get a list of authors. I just forgot to document it (forgot to document the entire Category endpoint actually). I'll add documentation later today.
I've also added a link to a few wrapper libraries (LEX4J, SharpLEX) in the API readme.

If you call the "/category/author" endpoint, it will return an array like this:
Code: [Select]
[
   {
      "id":1,
      "name":"ADMIN"
   },
   ...
   {
      "id":5275,
      "name":"zero7"
   }
]

Other categories (lex_category, lex_type) have a similar structure.


EDIT: Full documentation for the /category endpoint is now available: check it out at https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/documentation/Category.md (https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/documentation/Category.md).
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 12, 2014, 12:35:42 PM
Thanks :)

I didn't check endpoint folder :/
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 13, 2014, 02:45:57 AM
today at any API request I get an exception - something is broken :/
Title: Re: SC4D LEX API Source-code on Github
Post by: Tarkus on August 13, 2014, 03:03:59 AM
The databases for the forums were offline for about 30 minutes just now, and the LEX database went down at the same time, and is still down for the moment.  It'll sort itself out shortly.

-Alex
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 13, 2014, 03:38:03 AM
hehe ok, so my new request is to have some db status info via API - ie test if mysql_connect != false :)
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 13, 2014, 04:31:20 AM
Alright, the API will now return:

If the database is unavailable:
HTTP Status 503 - Service not available
Code: [Select]
{"status":503,"error":"Could not connect to database","suggestion":"Try again later"}
Other processing exceptions:
HTTP Status 500 - Internal Server Error
Code: [Select]
{"status":500,"error":"Message explaining the exception that occurred","suggestion":"Report to a site administrator"}
Documentation for the error modes and the basic endpoints (the API index and the version endpoint) has been added as well: https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/documentation/Base.md

This change will also be backported to api/v1 and api/v2
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 13, 2014, 04:37:57 AM
Thank you, works like a charm :)
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 14, 2014, 01:20:17 PM
Would it be possible to add image file size to primary branch of GET /api/<version>/search (images->primary) and/or to GET /api/<version>/lot/:lotid:, and add other images urls/sizes like secondary/right (where primary is left one), maybe only with specific param like getfullimageinfo=0/1 ?

Atm my app implements images cache - when search is executed images are downloaded to my HDD so next time I don't need to download them again but I wont know if they haven't change except url was changed by image reuploaded with different name. So next best thing to verify is file size.

I don't think that image file size information is necessary but I wanted to point some issues that only url info may cause.

ps. some images are over 2MB like here http://sc4devotion.com/csxlex/lex_filedesc.php?lotGET=3098, so this method is great at reducing server/end user bandwith in exchange of some disk space ;)

Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 14, 2014, 03:02:25 PM
While it's a cool idea (and reducing bandwidth usage is always good), I'm afraid implementing it could cause some issues. The biggest problem is that we don't currently store any image metadata in the database, so looking up things like filesize (or a file hash) would mean polling the file system to gather data like that each time a request is made, which has the potential to slow down the server response quite a bit. We could of course run a separate program to look up image filesizes/hashes/etc. periodically (on a cronjob or something) and have that put the image data in a new database table. But honestly, that seems like a lot of work for a feature that wouldn't be extremely useful in the API, and rather unnecessary for the LEX website.

You could probably store the images for a set amount of time (say 10 days) and download them again after that period. That should probably be enough for most applications - after all, I don't think many file authors ever change their file images.

However, I have added the two extra image types ('secondary' and 'extra') to the output of both the /search and /lot/:lotid: endpoints a few days ago in api/v3 ;)
See http://sc4devotion.com/csxlex/api/v3/lot/3129 (http://sc4devotion.com/csxlex/api/v3/lot/3129) for example.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 15, 2014, 02:54:32 PM
That's good idea with cache stored by specific amount of time, I will use this method - its easy and not time consuming to check file creation date/time.

So documentation was again not updated... next time I will check source code first :P

Thanks for your patience ;)
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 16, 2014, 09:42:06 AM
Heh, I'll admit documentation isn't one of my strong points. Then again, I don't know many people who do enjoy keeping documentation up-to-date. I'll try to keep them updated better from now on though ;)
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 16, 2014, 05:26:31 PM
its me... again :P

I have found an error in search functionality

https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/api/endpoints/search.php - line 253:

$sql = "SELECT * FROM LEX_LOTS WHERE " . $select;

this code assume that $select will have valid WHERE options but in fact there are possible outcomes of $select value that will generate invalid SQL query:

sc4devotion.com/csxlex/api/v3/search?order_by=recent
sc4devotion.com/csxlex/api/v3/search?order_by=popular
sc4devotion.com/csxlex/api/v3/search?order_by=download

will generate invalid sql: "SELECT * FROM LEX_LOTS WHERE "

atm only order_by=update return 'correct' output



btw atm order_by=updated returns entries that was updated at any time (regarding atm the date/time of update)... so is it actually correct to just output limited entries or it should be something like 2012 2013 2014 null null ?
query
select * from order by isnull(`data`), `data` asc

will return
2010
...
2014
null
...
null

- just a thought

Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 17, 2014, 01:43:09 AM
Quote
$sql = "SELECT * FROM LEX_LOTS WHERE " . $select;

this code assume that $select will have valid WHERE options but in fact there are possible outcomes of $select value that will generate invalid SQL query:

sc4devotion.com/csxlex/api/v3/search?order_by=recent
sc4devotion.com/csxlex/api/v3/search?order_by=popular
sc4devotion.com/csxlex/api/v3/search?order_by=download

will generate invalid sql: "SELECT * FROM LEX_LOTS WHERE "

atm only order_by=update return 'correct' output

That is actually the way it is supposed to work (if you try sc4devotion.com/csxlex/api/v3/search?order_by=recent (http://sc4devotion.com/csxlex/api/v3/search?order_by=recent) for example, it will fail with 400 - Bad Request (https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/api/endpoints/search.php#L178) (and never actually execute the 'broken' SQL), because there are no actual "filters" in such a request, only an ordering. ?order_by=update, however, does work, because ordering by update also implies a filter (the file was updated at least once).

However, looking at it now, that approach is a pretty useless limitation without any good reason, so I will be changing that soon. Hopefully today, but it could be later next week (have some important Uni stuff coming up).

Quote
btw atm order_by=updated returns entries that was updated at any time (regarding atm the date/time of update)... so is it actually correct to just output limited entries
Yes, this is also intended behavior. I modeled it (like most of the API) after the website functionality of the LEX. If you check the "Recent Updates" (http://sc4devotion.com/csxlex/lex_lotlist.php?orderBY=update) page in the LEX itself, you will see that it too only returns files that were updated at least once. Files that were never updated do not appear in that list.
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 17, 2014, 01:56:22 PM
Quote
$sql = "SELECT * FROM LEX_LOTS WHERE " . $select;

this code assume that $select will have valid WHERE options but in fact there are possible outcomes of $select value that will generate invalid SQL query:

sc4devotion.com/csxlex/api/v3/search?order_by=recent
sc4devotion.com/csxlex/api/v3/search?order_by=popular
sc4devotion.com/csxlex/api/v3/search?order_by=download

will generate invalid sql: "SELECT * FROM LEX_LOTS WHERE "

atm only order_by=update return 'correct' output

That is actually the way it is supposed to work (if you try sc4devotion.com/csxlex/api/v3/search?order_by=recent (http://sc4devotion.com/csxlex/api/v3/search?order_by=recent) for example, it will fail with 400 - Bad Request (https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/api/endpoints/search.php#L178) (and never actually execute the 'broken' SQL), because there are no actual "filters" in such a request, only an ordering. ?order_by=update, however, does work, because ordering by update also implies a filter (the file was updated at least once).

However, looking at it now, that approach is a pretty useless limitation without any good reason, so I will be changing that soon. Hopefully today, but it could be later next week (have some important Uni stuff coming up).

This change has now been pushed to api/v3. ?order_by now always counts as a filter, not only when it's ?order_by=updated. Relevant documentation has also been updated, and a few other small backend changes have been made to the api.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on August 17, 2014, 03:02:31 PM
I didn't check all updates but order_by works ok (with a few queries) :)

Thanks


any chance to get result file from "Download dependencies" button via api ?

------------
regarding value 'rating' from

POST /api/<version>/lot/:lotid:/comment

what's this 'rating' value? is it value of votes green/cream/pink like in http://sc4devotion.com/csxlex/lex_filedesc.php?lotGET=2876 ?

if yes how can I retrieve it by API ?

Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on July 20, 2015, 06:37:46 AM
ha long time...

locked files doesn't return comments...

i.e.

v2 or v3 - no change

http://sc4devotion.com/csxlex/api/v2/lot/2675/comment
http://sc4devotion.com/csxlex/lex_filedesc.php?lotGET=2675

http://sc4devotion.com/csxlex/api/v2/lot/3196/comment
http://sc4devotion.com/csxlex/lex_filedesc.php?lotGET=3196

http://sc4devotion.com/csxlex/api/v2/lot/1734/comment
http://sc4devotion.com/csxlex/lex_filedesc.php?lotGET=1734

result is an empty string...

Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on July 21, 2015, 01:36:57 AM
Seems like I was retrieving only comments for non-locked files by accident. I'll fix that as soon as possible.

As for the rating numbers (god I'm 1 year late to replying to that - sorry!), the 1 to 3 do indeed correspond to the LEX "Feedback for the author". With 1 being "Thank you very much" and 3 being "I don't like this". I don't believe this is currently retrievable, but it makes sense to have that functionality, so I will add it soon as well. However to preserve "anonymity" it will only be possible to retrieve the total votes for each category and an average.

To get the dependency list, all you need to do is call /lot/<lotid>. If it supports the dependency tracker, it will return something like this:
Code: [Select]
"dependencies":{
      "status":"ok",
      "count":2,
      "list":[
         {
            "internal":false,
            "link":"http:\/\/www.simtropolis.com\/forum\/files\/file\/11421-porkie-props-vol1-european-street-accessories\/",
            "name":"Porkie Props Vol 01"
         },
         {
            "internal":true,
            "id":1263,
            "name":"BSC Mega Props - JES Vol05",
            "status":{
               "ok":true,
               "deleted":false,
               "superceded":false,
               "superceded_by":-1,
               "locked":false
            }
         }
      ]
   }

To get the actual response html, you can request http://sc4devotion.com/csxlex/lex_deptracker.php?lotID=<lotid>.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on July 21, 2015, 03:07:18 AM
god I'm 1 year late to replying to that - sorry!

better now then never ;)
I was inactive with my SC projects for a year too ;)

Thanks
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on July 21, 2015, 04:15:17 AM
I've fixed the comments for locked files (in v3) and added the vote retrieval functionality (in v3: http://sc4devotion.com/csxlex/api/v3/lot/1734/vote (http://sc4devotion.com/csxlex/api/v3/lot/1734/vote)). It's a very simple endpoint, just a mapping "rating" => "number of votes". I may incorporate it in the normal /lot endpoint later.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on July 22, 2015, 04:08:04 AM
That was quick response :)

Actually it would be better if this data would be available in GET /api/<version>/search in concise=false and/or in GET /api/<version>/lot/:lotid: - ATM this require additional queries which result in server resource drain :)

In addition it would be great if search and lotinfo JSON would return additional data like: "lex category" and "lex type", "downloads count".

Comments: don't know why in v3 comments are deprecated (other than 'disqus'...), old comments are and will be still available (?), so some additional data in comments JSON would be nice: "is author", "is admin", atm I can connect comments made by author by list from GET /api/<version>/category/author... ("is admin" could be useful for locked files - where admin state reason for locking it).


Thanks
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on July 22, 2015, 04:35:39 AM
I will add the voting and comments to concise=false for lot and search soon.

Download count is already returned by lot_info & search (the "num_downloads" property). However, lex category and lex type are not. It makes sense to add that, however, so I will.

Comments are indeed not deprecated anymore. The experiment with disqus was not well-received and scratched. I believe the comments JSON already includes is_author: true/false and is_admin: true/false, both in v1, v2 and v3. See http://sc4devotion.com/csxlex/api/v1/lot/1700/comment (http://sc4devotion.com/csxlex/api/v1/lot/1700/comment) for example.
Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on July 22, 2015, 05:16:59 AM
I believe the comments JSON already includes is_author: true/false and is_admin: true/false, both in v1, v2 and v3. See http://sc4devotion.com/csxlex/api/v1/lot/1700/comment (http://sc4devotion.com/csxlex/api/v1/lot/1700/comment) for example.

ups... my mistake, didn't even check if data is there 'already'  ::)
Title: SC4D LEX API Source-code on Github - v4 work started
Post by: CasperVg on July 23, 2015, 02:19:06 PM
I've started work on a v4 of the API (https://github.com/caspervg/SC4Devotion-LEX-API/tree/v4-dev), that will incorporate your suggestions and also unify the interface (especially the /search and /lot/<lotid> results will be more uniform).

Dependencies can be requested with ?dependencies, comments with ?comments, votes with ?votes and last_downloaded with ?user. Also date and datetime fields will now properly follow the ISO-8601 standard (I believe).

So for example:

http://sc4devotion.com/csxlex/api/v4/lot/3146?comments&votes&dependencies&user (http://sc4devotion.com/csxlex/api/v4/lot/3146?comments&votes&dependencies&user)
(requesting Lot 3146 with comments, votes, dependencies and user info)

Will return:
Code: [Select]
{
    "id": 3146,
    "name": "SCILT, BSC Diagonal Tile Version with Sidewalk Mod",
    "version": "1.0",
    "num_downloads": 47,
    "author": "c.p.",
    "is_exclusive": false,
    "description": " \\\r\\\n\\\t\\\r\\\n\\\t\\\t Sidewalk Compatible Interchangeable Lot Textures. This is a collection of lot textures intended to match the texture of your sidewalk mod. Note that for this version, the sidewalk mod is included, meaning the sidewalk textures in your game will change to this texture. The lot textures should be useful on lots where the sidewalk texture needs to be extended onto the lot, or for paths, plazas, patios, etc.  \\\r\\\n\\\t\\\r\\\n\\\t \\\r\\\n\\\t\\\r\\\n\\\t\\\t The different versions are interchangeable, meaning if you remove one version from your plugins and replace it with another, the game will show the new SCILT textures on your lots in place of the old SCILT textures. \\\r\\\n\\\t\\\r\\\n\\\t \\\r\\\n\\\t\\\r\\\n\\\t\\\t The texture is from BSC Textures Vol 02. \\\r\\\n\\\t\\\r\\\n",
    "images": {
        "primary": "http:\\/\\/sc4devotion.com\\/csxlex\\/images\\/beximg\\/LEX_SCILT_BSCDiagonalTile.jpg"
    },
    "link": "http:\\/\\/sc4devotion.com\\/csxlex\\/lex_filedesc.php?lotGET=3146",
    "is_certified": true,
    "is_active": true,
    "upload_date": "2014-12-12T00:00:00-0600",
    "update_date": null,
    "filesize": "733.68 KB",
    "comments": [
        {
            "id": 36889,
            "user": "caspervg",
            "text": "Thanks a lot for this wonderful creation!",
            "date": "2015-07-23T00:00:00-0500",
            "by_author": false,
            "by_admin": true
        }
    ],
    "votes": {
        "1": 2,
        "2": 0,
        "3": 0
    },
    "dependencies": {
        "status": "ok",
        "count": 1,
        "list": [
            {
                "internal": true,
                "id": 638,
                "name": "BSC Textures Vol 02",
                "status": {
                    "ok": true,
                    "deleted": false,
                    "superceded": false,
                    "superceded_by": -1,
                    "locked": false
                }
            }
        ]
    },
    "last_downloaded": "2015-07-23T14:25:21-0500"
}
(http://caspervg.net/codedrop/view.php?a3d2a560b1d7796f9b476a97eb30ce0b)

Title: Re: SC4D LEX API Source-code on Github
Post by: Yild on July 29, 2015, 12:59:39 PM
any ETA when v4 branch will become master ? :)


btw

http://sc4devotion.com/csxlex/api/v1/ (v1-v4) there is an info pointing to http://sc4devotion.com/forums/index.php?topic=16074.0 but this URL gives me "The topic or board you are looking for appears to be either missing or off limits to you." error
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on July 29, 2015, 01:53:34 PM
Heh, that's actually a very old thread, documenting my first iteration of the LEX search api ( located at http://www.sc4devotion.com/csxlex/lex_api_search.php ). I'll remove the link to it since it is a private thread. Everything you need to know is in https://github.com/caspervg/SC4Devotion-LEX-API/blob/master/documentation/Search.md

I'm hoping for an official v4 release this weekend. The main "issue" right now is updating the documentation.
Title: Re: SC4D LEX API Source-code on Github
Post by: CasperVg on August 02, 2015, 11:27:08 AM
LEX API v4 is now officially released and available at http://sc4devotion.com/csxlex/api/v4. Updated (hopefully complete) documentation as available in the Github repository (https://github.com/caspervg/SC4Devotion-LEX-API/tree/master/documentation)