[QUESTION] Design Help/Ideas - Trade Database

[JayNic]

Hey all, I'm looking for some guidance and ideas for my trading database (I'm calling it Tradabase).

 

Basically, the most annoying game-ification in the game (to me) right now is the fact that I can't see who buys and sells what, even though that information is freely given from the stations. As of right now the player has two choices to understand where to buy and sell:

1) A trade computer

  - This module really just does math for you. And by the time you get a good enough one that goes back a few sectors in history: you're already making enough money to run have the galaxy

2) A spreadsheet

  - Nobody likes spreadsheet... let alone how detracting it is from gameplay when you are being pulled out of your immersion to fill out data

 

So I started to write a little mod that records what every station buys and sells whenever you enter a system. I've got this part working.

I grab all the stations, and all the goods they buy and sell, and dump the variables in to a text file that I create.

 

The end idea is some sort of interface to be able to pull up and either browse or search for goods - but that's for later.

 

What I've looking for next is some ideas from the community on potential issues.

 

Namely the size. The data has the capacity to get really big, and bog down performance on the client (the trade data is recorded, and stored by the client NOT the server). So being able to load all of the potential sectors in to some tables to peruse could cause quite an issue.

I was thinking maybe I could have a drop down with a faction, and the tradabase window would only then need to browse the goods in a given faction. This way: I could store the data against a faction, and limit the size during consumption.

 

Anyways... any ideas/suggestions/help would be great.

 

[JayNic]

I WAS thinking about an external db too... I'm quite experienced with good ol php/mysql. This is the first real project I've ever tried to undertake in LUA - (the only reason I decided to do it was because of the good documentation, and the fact that this game is really well programmed.)

 

Definitely want to share it. Just wanted to get over this hurdle before I move forward - cause it could be a show stopper.

 

So you're saying that you don't think the file parsing/searching would be too bad?

 

Cause here it what I'm thinking if I break it down in to single responsibility modules.

 

 

Module 1) Data gathering.

This is pretty much done. This part gathers all the stations buy/sell data when you enter a sector, including the current price. I can add more things like you say - for example the person who discovered it. etc...

 

Module 2) Data storage.

Store all the data as the player plays in a bunch of tables. These tables can be sorted and interfaced with through the scripting api. This is the "database" part - but just a flat table, really.

 

Module 3) Parsing stored data

HEre's where I have some black holes in my mind - cause I'm not sure I know enough about lua/the game to answer these questions... If I was to store EVERYTHING the player had discovered in a single text file - where each line is a single good at a single station, then maybe I could just load that entire text file in to memory when the player joins up, store it all in to the tables, and the interface takes over from there.

Then when the players LEAVES the game... (or maybe even leaves a sector) I just write the whole thing back to the text file... Seems like it might be overkill... like: my programmer brain tells me that I should really just write a single text file per sector... but my lazy/simple brain tells me I should just do one big write at the end of it all.

 

If I do one big write: then there is the chance that during a crash: the player will lose his data cause he won't kick off the onPlayerDisconnect event which is where I would trigger the write...

 

 

So I guess... If in your opinion: parsing all those text files when the player opens the "tradabase" window won't cause a nasty cpu hit: then maybe that's the simplest way?

 

 

Thanks for your thoughts, btw. Greatly appreciated.

 

[SkeletonKing]

Had to shed that old skin.  Now that I am looking at this again.  I worry about concurrent connections and issues organizing something like a JSON file.  Even if the player crashes something like Sequelize would work well because it is a promise based ORM.  Seems complicated or hard... after you start using it, it becomes really easy.  I'll DM this too, hopefully it will stick after I am banned again.  Oh mods, no thoughts, no communication... just ban ban ban

[SkeletonKing]

So far here is my process on Ubuntu Server 16.04 LTS (all free btw)  this is a WIP to test this out.  for windows you can http://www.postgresqltutorial.com/install-postgresql/

 

sudo apt-get install postgresql

sudo apt-get install postgresql-client

sudo /etc/init.d/postgresql start

 

sudo passwd postgres *some new psswd*

sudo usermod -aG sudo postgres

 

had to install postgres client manually with sudo apt-get install postgresql-client-9.6

sudo update-rc.d postgresql enable      //runs postgres on startup

sudo apt-get install nodejs npm

sudo npm install -g sequelize

 

ok now for postgres stuff https://www.postgresql.org/docs/9.5/static/tutorial-createdb.html

 

sudo su postgres

createdb tradnet

psql tradenet

 

CREATE ROLE tradmin WITH SUPERUSER;

 

//looks like the postgres server is good to go.  if it isn't that won't be too big of a deal

 

https://github.com/arcapos/luapgsql/blob/master/luapgsql.adoc

looks like someone has a recommended GH repo that can make information from the lua script go right into the DB... hopefully

 

 

 

 

[dwarvenlord1]

It seems to me you are going to want to use a local Database of some type, and it is going to have to be a file based one similar to access.  Any other way is going to take the user installing a database program and configuring it properly which is going to be a nightmare trying to help people with different operating systems,etc.

 

If it is an online data base: users x different seeds x visited sectors x number of items  x number of mod users = face palm.

 

It should support sql queries, or the like.  Ideally, you need to save the seed, which will be the unique I'd for the current game.  You are also going to want to store sector coordinates for range searches.

[JayNic]

I'm hoping for the simplest possible solution with extremely easy installation. I hadn't really considered using an actual database because of the over-all complexity of the implementation. Add on to the fact that this game is still early in it's existence: the chances of architectural changes that will result in re-writes of the system would be pretty high...

 

As it stands, I don't think there is a way to save any data directly to a player - that will persist across his play through. I think you can save data to the SERVER but even that (I read somewhere) is currently bugged... Additionally: I'd like to have all this work being done by the client, anyways.

 

And yes, you're correct dwarvenlord1; saving the seed is the unique identifier. I wrote that in already, and was playing with just appending it to the file.

 

 

If I go to an actual database: I'd want to just put it up on a webservice somewhere. Then build a basic JSON api to grab data. This then involves getting a parser in lua, and converting the results to lua tables... It's probably the most reliable model: because then the player is a pretty slim client... But... then there's always worrying about versioning... Ensuring the client is up to date, or maintaining older versions of the api... blech... This is sounding more and more like my job... (developer/consultant)

[SkeletonKing]

Firebase is looking much better.  The limiting factor is 2,000 uploads a day (for the free version).  Doubt every sector would even need to be uploaded. https://firebase.google.com/pricing/

 

You basically just log in and it click the security \ db you want.  There isn't a set file structure.  I had my phone sending coords to firebase for a while lol. 

 

This I could test out later.  I will try to send something from lua to the db... not sure what yet.  Also firebase has this amazing feature, when it says it is running.  It is actually running a database with out 4 hours of troubleshooting!

[Devious]

This definately would be very useful untill the ingame map allows us to search for specific types of stations.

I am very interested in trying this, so I'll try to give my view on this.

 

After messing about with a few spreadsheet and msoffice databases, I reached a few conclusions.

While trading not all goods are noteworthy, some products only yield low profits compared to the space they require.

Therefore I only started logging the stations with +30% selling prices or -30% buying prices for good with higher base values and quantities.

For building stations and supplying them however, it would be more useful to map all goods with decent stock and positive selling prices and negative buying prices.

By eliminating the stations that sell at bad prices (usually due to low stock), I managed to save some time on the spreadsheet by doing this.

 

After a while the spreadsheet was so full the only way I could find something was by searching for the goods manually, at which I tried to input it all in a msoffice database but I'm not really experienced with that software so I need more time before I will get that working. But that should give for an easy way to input data and get something useful out of it such as own traderoutes.

 

On a global level it would be best to store the information per seed, but on a player level per faction would suffice for the most general application as well. While using the spreadsheet I found that I could recall the faction that was selling or buying a specific good most of the times, but spend most time on finding the actual sector if I tried searching for it manually.

 

Therefore it would be the most useful if the data logged also provides the coordinates, that way you can always find back the station and it's goods by searching through the datafile and specifing wether you are looking to buy or sell the specific item.

[JayNic]

Yeah Devious: that's the idea! I was thinking of creating a spreadsheet, and I just thought to myself how annoying it would be to have to input/search for data. Then I though I could just output the textual data for easy copy paste IN to a spreadsheet - at which point I decided there wouldn't be THAT much more work to create a full in game searchable db...

 

I think for simplicities sake: I'm going to continue along the line of the pic I posted:

Create a text file to store the data in each system the user goes to. The file name contains the galaxy seed (but with non-alphanumeric replaced with underscores, in case I can't create a file with funky symbols) as well as the coordinates

 

I'll store the following info:

Station name/type

Good name

Buy or sell

last unit price (of the player)

stock

max stock

 

That's all!

 

Then I'll monkey with the ui scripting to build a basic paginated list. Hopefully an input text box to allow the user to type in what they want to get. That, or a picklist type deal to select a particular good.

 

That'll be it for now. I'm REALLY not experienced with LUA, and haven't really looked in to the UI scripting api, so that is more than enough for me to bite off.

 

Long term: if this fails horribly: then I move the database to a webservice, store all kinds of stuff with simple little GET params, then integrate a more complicated search feature that actually fires off a request, performs a query on a PHP/MYSQL backend, and returns a parsable table that gets interfaced in to the ui script. That's the ferrari version.

 

Anyways - wish me luck!

[Antimodes201]

How are you pulling the list above?  Database and a web front I can do but I don't know LUA (guess I got some learning to do there).  If we could do the list above and maybe append the seed this can become an rather straight forward insert and the lookup is just a matter of a clean select.

 

The question would become how often trade stock updates?  I know factory stock updates as time advances since it creates new items.  Do traders and stations update their stocks as well, if so we would need to do a new insert each time you enter a sector.

[JayNic]

Antimodes,

 

I've taken to storing the data as lua tables - these can then be directly parsed back to objects with loadstring. For now: I'm not going to go external DB/api route... I'll keep it simple.

 

I have them pretty-printing now and they store in a trext file in a pre-defined path:

 

- I'm going to have the file written each time the player enters, or leaves a system. That way the data is always up to date.

I'm toying with the idea of simply storing all the data in memory during play: and only writing on disconnect... But if there are crashes - it might mean the player loses his play-data... so maybe not...

 

 

[Devious]

I wouldn't load them in memory on multiplayer servers, the updating of every sector after a player leaves could be a lot on performance as well as we can see with the OOSP mod but perhaps it's not as intensive.

 

If you store the data per sector, how do you have it in mind on bringing the data back together so it can be searched through on a galaxy/faction level?

[LogicSocket]

Liking the progress JayNic!

[JayNic]

Devious, I'm making sure I'm doing this recording on the client. The server only has to provide the information: because it doesn't look like I can get the goods from a call on the client... which kinda sucks... But the most minimal amount of work is done by the server: the client will store it in memory. I'll test whether or not the memory get's too bogged down - but I'm not anticipating it.

 

Push comes to shove: and people are saying their ram is through the roof: then I'll ONLY load the data at RUNTIME on the client when he opens the browser: and I'll ONLY permit the user to search within a given faction or maybe within a limited radius... Hopefully it doesn't get to that point.

 

It feels good to have actually made a solid step... It's a far cray from me not knowing any lua since I started on this.