Report Archive

[eX0du5]

Hi,

I want to collect some ideas for a feature often requested: store more than one report for coordinates.

I thought about something like a report archive where you can add reports manually or automatically.

But there are some basic questions which I want to discuss with you first:
- How shall reports be inserted to the archive ? I personally do not prefer any automatic things because I could then overwrite existing entries in the archive. Or if there is no limit for the number of reports in the archive it could blow up with reports which are absolutely senseless. (No fleet scanned or only scanned up to resources)
- How could automatic archiving look like ?
- How would you like to see the archive in the galaxytool ? I think it would need a new page just for the archive, but what options would you need to compare them ?

Restrictions from ogame:
- Reports do not have a year in its date yet, therefore I can only guess the year. So if you add any report from 2006, it would be 2008 or 2009 then.
- There is no 100% linkage to a player yet. There is a playername and planetname shown in the report, but both can be changed within ogame. Therefore the Galaxytool cannot be sure to which player a report belongs to. Currently the planetnames are compared only. I will change this as soon as the playername becomes a hyperlink. Then we have a 100% linkage from reports to players.

[eX0du5]

Answers from the old board:

walten:
when the toolbar sends a new spy-report the new info could have a special color. Ex green. And if some data is missing in the report, the old data allready in the GT could have another color.

Just like the colors used in the 'STATUS OF DATABASE'


mehammered:
in game stats it will give us a graph of the overview so fleetrank and points, I am talking about the same graph set up but for each moon/planet witch will show if I where to probe every hour a line giving me the details of that planet or 1 day 1week or 1 month. Fleet value. in cress or de cress in res etc. Oscan had it so you could track that just by adding the graph model and saving your reports and comparing them to each other.

At this time this Gal tool does not is there any way to add that to it?

the guy put up the code so that others could us it to better there data bases because he quit

http://sourceforge.net/projects/oscan/


nightwolf:(regarding mehammered's post)
I think it`s a good idea. it makes sence to change between changes per day (if u got more than 1 spioreport that day) - week and last 30 days. is it possible to include that function in the tool?

[Jmb]

- Reports do not have a year in its date yet, therefore I can only guess the year. So if you add any report from 2006, it would be 2008 or 2009 then.

but does people really have an old esp report and insert it in gtool? I meen, in ogame messages and reports last a really short time. so to be able to insert it to galaxytool you would have to save it for over a year and manually insert it.

[eX0du5]

And if you enter a report from November I shall always guess that it was from last year if we are in a month != November or December?


Until now the year was not really needed and I don't see a reason to keep reports in the archive more several years.

[Jmb]

I originally was thinking in use the year from when you insert the report.

to overcome the problem when you insert for example in January first a report from the last week of December. I would compare the actual date (only day and month) with the date that the report have.

if DATE_REPORT > TODAY then the we would assume that the report is from last year.

I seriously doubt that someone would save a report over a year and then insert it to the database. this would be used in cases when the year change is near.

[ricc]

I would be more than happy with something like:
1. Store the most recent report
2. Store N reports (N=5 or 10) where the reports are either the most recent ones or reports marked for "keeping".

[Caffeine Addict]

The year problem would only be a problem nearing the end of a year, and only if people had very old archived reports in their galaxytools.

I would suggest using a php variable that would use the server year + the report time information combined. I don't know alot of php to know if that is possible but it is a suggestion.

I would also like to be able to continue storing reports, without limit. Then add an Admin option to delete reports older than a specified date.

[antikiller]

Jmb proposed very good solution for the year problem.

And some configurable options:
- store N last reports for the planet
- store reports for max X days (months/years).

[Jmb]

I'm working in a way to track the posible online times based on the espionage action reports.

but while doing so I tested to see if my parser could figure out the compleate date, so check it out.

so the idea is to assign the year when the esp report is inserted so you can put the whole date.

Code: [Select]

$thismonth=date(m,time());
$thisyear=date(Y,time());

if ($thismonth<$month)
$year=$thisyear - 1;
else
$year=$thisyear;

I think that this could be useful in the cases where the year just changed

$month = month form the esp report.
$year   = year for the esp report.

it would be good to identify if the esp report that the toolbar is trying to upload is already inserted. may be checking who uploaded it, the date and the coords.

[warliuks]

dunno if i wroite in good sectio but... why i updated esopionagre reports with resaerches to gt database, and then my friend with lower espionage lvl updated too reports, and all my work is gone, all ppl havnt info about researches.. why? could you make that if espionag report is not ful, the nort deleted earlier info with more buildings researches and etc...

[Warlockzzz]

Do an insert date on each report inserted inside the database.

On insertion, i saw that u guys use a replace query which trigger a delete trigger and an insert trigger, so, no php code neede here. Only need to add a new table. I'd say kept all report in database. Set up a size limit in the administrator option with action to be taken if size go over(ex : delete oldest, empty history report that are older than, empty history report, warm administror on connect until a certain limit, [...]).

This kind of system would allow report with more information to be erased and still be avalaible.

An interface like the one showing the galaxy should be created to handle the report.
Then again, i'd say show the last 24h unless the person specify for more.
Allow user to view some progress report using player defined choice.

It would allow to see progression of planet, research and when the player is the most active(and the inverse).

[warliuks]

can you make history off player old names, coz many players changing many times nick and later cant remember who he was... (like with ex alliance).

[Nommy]

Hi, is anyone still working on this? I just noticed it's actually quite an old topic, but I can offer some ideas / response / help perhaps.

I'd prefer if reports were inserted automatically and there were options to control how many or how long to keep a history of. Perhaps something like delete reports older than 1 month except to keep 1 full report for each day or each week so you can do time graphs if you wanted. Lots of people like stats I think

Also I'd like to be able to do a search on say inactive player planets near my colonies that showed resources from a recent scan, and defenses from another earlier scan if the most recent report didn't have that info. And I'd like to see the activity you get in the report that says 48min ago someone scanned it or whatever, so you can decide where to attack that hasn't recently been scanned, perhaps. (I dunno - just started OGame 4 days ago).

As far as the archiving goes, you could minimise space by storing the different sections of the reports separately e.g.
   ReportResources table - linking to Reports, containing resource values
   ReportFleets - linking to Reports containing fleets info
   ReportDefenses - linking to Reports...
   ReportBuildings - linking to Reports...
   Reports table with date, user, etc, and fields like ReportResourcesID, ReportFleetsID, ReportDefenseID and ReportBuildingsID
   
Reports with just resource info would take little room this way, but it’s a trade-off with the overhead of linking the data and code complexity I guess.
Also you could put fields in the Galaxy table containing the IDs of the most recent Resources/Fleets/Defenses/Buildings info so it's quick and easy to get the most up to date data for a planet which may originate in different reports.

I'm thinking a compound key of scantime (as datetime) and galaxy+sysem+planet might be useful for those 4 tables. Duplicate keys wouldn't be a problem I don't think - if a 2nd report from another user for the same planet at exactly the same time arrived, it could just link to the existing report detail records I guess, since you’d expect the info to be identical. If this type of key was used, you could use the scantimes instead of an IDs in the galaxy table to link the most recent Resource/Fleet/Defense/Building info. I guess you could further save space by having an ID for galaxy records and using that instead of galaxy+sysem+planet.

It could also save some space if the planet name and other common info was recorded only in the galaxy table, and not in the reports table as well, but I guess then you'd have no way to track planet name/ownership changes then.
One alternative would be to have a table like PlanetOwnership or PlayerPlanets or something to record the name of the planets and who owns them. It could reference galaxy table and be referenced by reports, so there wouldn't be 1000s of varchar records in the reports. Would make it easy to see the history of a planet which changed hands a few times, if there was a record with a date for each time it was colonised/renamed/abandoned. But again space saving vs complexity I guess. Kind of makes sense though I think, having a table (galaxy) for the actual planets, another to record the ownership details of those planets which is referenced by reports and report details that contain only minimal data.

Is that the sort of thing you wanted to discuss when you wrote that? Hope so . Perhaps it's already done. I haven't really thought it through much, just some ideas.

Actually I started doing something similar yesterday, separating the report details, then I realised that only 1 report was saved for each planet - bummer. Anyway, here's the sql I was using to change the scantime to datetime datatype which might save you a couple of minutes if you haven't already done something similar

Code: [Select]

update capella_reports
set scantime2 =
  Cast(
    If( (Length(scantime) < 14),
      # scantime is blank or something unexpected, use zero datetime
      '0000-00-00 00:00:00',

      If( (Length(scantime) = 14),
        # assume scantime is mm-dd hh:mm:ss format, prefix the year
        Concat(Year(Now()), '-', scantime),

        # Length(scantime) > 14
        # assume it's yyyy-dd-mm hh:mm:ss, just use unmodified value
        scantime
      ) #end if
    ) #end if
  as datetime)v4_5_3 btw

I'd be happy to help out with the SQL side if you can use any help there. I don't know any php, and only used MySQL for the 1st time yesterday, but I've used SQL a fair bit in SQL server and stuff. I'm interested in getting a report that shows recent resource info with older defense info. For me, SQL/DB side won't be a problem I don't think, but changing the php will. I don't suppose you've got a version that keeps multiple report records lying around anywhere that I could get hold of do you?

[Edit]Ah, I just read that galaxy tool isn't open source - sorry I shouldn't be changing stuff, I didn't realise before. I just assumed it was open source, probably coz all the source is installed. I can get rid of the code above if you like, but I thought I'd leave it just in case it's of any small use to you guys. I did wonder why there was no mention of an SVN repository anywhere though. I'd still be interested in helping, or getting a more recent version for my own personal, use to fiddle around with here if that would be ok? I'm just running it on my local computer and it goes without saying I'd not pass it on to anyone etc. I'm not sure how strict you guys are about it and understand if you don't want to do that, but I thought I'd ask all the same.

[eX0du5]

Just to inform everybody here - the date is no longer an issue. It is submitted with a year by the new firefox extension which is currently out for review at mozilla. If this is released, a new version of the galaxytool will come which also stores the full date.
Now I am collecting the IDEAS in this thread to think of the possible solutions, so writing DB queries won't help too much at the moment. The new version of the galaxytool will require mysql 5.x and therefore we can rely and use trigger as well. I am doing this then for the player and alliance history and this will be used to insert the reports automatically into the archive.

From what I read so far, people are interested in

• standard analytics applications to compare reports
• clean up old reports easily
• search in old and new reports if the new report does not contain information the next older report contains (e.g. defense)
• See when a player was active --> this cannot be done as this is also not part of the reports yet

Anything else?

[bontchev]

Information about when the player was (supposedly) active can be collected also from the Galaxy view.