Having to rework code on this since I lost a backup file and didn't do cloud storage for old query and python files.
To implement importer fully, you should have the pyodbc module. A c bindings driver that interfaces that provides pythonic interface commands for ms sql.
At the moment its still needs more work, you just need to set up at the program's outset a directory path to Eve market logs folder. There are three main table types where data is sent: reactionmaterialsindex, itemsindex, and myorders. Also you'll need to make sure that you enter correctly your sql server database information, alongside user id, password inputted. I may eventually get around to creating a windows based executable but for now you'll need to do this manually.
Dependencies:
python 2.7 + (works with 3.2)
pyodbc
MS Sql database2012 (hadn't checked other versions)
Eve fansite kit
Evesqlimporterscript
Windows installation procedure:
Download and install the above program dependencies. You'll obviously need to install python2.7 or 3.2 before you install pyodbc. Just grab the appropriate pyodbc module based upon your system and python version. Then install based upon your system, the MS Sql database 2012 with tools option. Then grab the fansite kit (just need Inferno_1.0_70633_db.zip).
Once you've installed MS Sql 2012, run Microsoft SQL Server Management Studio. Firstly I recommend in Object Explorer (left hand side of Studio window) right mouse button clicking on 'Databases' , here select 'New Databases' and then input in the Database field name 'Eve'. Press Okay. Next for the pyodbc driver you'll probably want to have SQL authentication enabled, so you'll need to enable this authentication by selecting in the Object Explorer, your Server Name, mine is for example, CHRISTOPHER-PC\SQLEXPRESS, it is the top most object item in the explorer's data tree structure. Right mouse click on this, and select 'Properties'. Then under 'Select a page' (left hand side of pop up dialog), go ahead and select, 'Sql Server and Windows Authentication Mode'.
Next you can create your Sql authentication based user id, by selecting in Object Explorer, YourServerName > Security > Logins. Right click on 'Logins' and select 'New Login'. Under 'general' (Select a page tab) of your 'New Login' dialog, select 'Sql Server Authentication'. Then enter your 'Login name' in the provided field, make note of this, you'll need to add this information to the python script that I have furnished. While there are many options under the 'Select a page' (left side of dialog, under 'Server Roles', 'User mapping', and 'Securables'. By default granting permission and check marking all fields for 'Server Roles' and selecting your 'Eve' database, should grant you necessary permissions for your userid. Make sure you have entered a password. This has a minimum characters requirement default, so make sure this is at 15 alpanums in length (at least I haven't been able to input anything less).
Next using a favorite text editor or using Server management Studio direct, copy and paste the ebs_DATADUMP sql query data into a new query. On Server Management Studio, you can select New Query, making sure that YourServerName > Databases > Eve is selected, and then paste the sql query code into the new query window that should be populated, or open the ebs_DATADUMP file directly from Server Management Studio. Then press execute. If all goes well should have new tables (with no data) furnished on your new Eve database.
Next you'll need to restore the backup database file for Eve. You can do this from Object Explorer, selecting YourServerName > Databases and right clicking Databases, you can select 'Restore Database..' I've found that owing to the silliness of the file finder dialog here to have the DATADUMP201205212008.bak (or related file depending on what version of Eve is presently current) having this setup in a convenient locations like 'C:\Users' by default. By default under the 'general' tab, select under 'Source' the 'Device' radio button. To this right of the now blank field under this radio button, select the '...' button. To the right of the presently blank 'Backup media' text field, select 'Add'. Then go to your source directory for the .bak file using the pop up directory tree explorer. Your backup file should be present in the file field. Select this, press Okay, and then push Okay again on the 'Select Backup Devices' dialog. You should now have the .bak file showing on your 'Restore Database' dialog now. Under 'Select a page' of the 'Restore Database' dialog, you should select 'options'. Then select 'Overwrite the existing database (WITH REPLACE)' and then select 'Preserve the replication settings (WITH KEEP_REPLICATION)'. Push 'Okay' on 'Restore Database' dialog here. If all goes well you should see a confirmation that your database has been restored. You can check for data in this database, by selecting in Object Explorer,
YourServerName > Databases > Eve > Tables and then selecting any one of the given 'dbo.' tables shown. You can right click on the table, and select 'Select top 1000 rows' . This will automatically generate a query for you populating database information for the given table. I highly recommend that you have some background in SQL, so that you can create your own custom queries likewise. Any decent book like SQL Visual Quick Start guide by Peach Pit Press publication for instance, can be an excellent start for learning some basics to query language programming. I also recommend some requisite knowledge of python here to aid you likewise.
Now that you have data in the database, using your typical market export methods in game, and presuming you have something in marketlogs folder to import into the database. You'll just need to furnish your server info, userid, and password at the 'cnxn' variable of the Evesqlimporter script. And put the directory path to your Eve market logs folder at the 'dirpath' variable. After running the script you may need to close down the existing sql server socket connection manually to populate say added data in Server Management Studio. Apparently I've run into difficulties with more then one server socket connection utilizing pyodbc and server management studio at the same time. In this case, just close out the idle terminal, not the script.
Finally while I have a batch file move script, to place a file in a given backup destination, I haven't implemented this. You could do this on your own if you like, or move the files to a backup destination other then your present directory. I may add for convenience sake later a file sync script, that records and tracks a list of uploaded files into the given database, thus avoiding having to do anything further other then making sure that files added to the folder or import merged into the database once synchronization is enabled.
Updates 6/11/12:
Materials Reaction Average indices python script
EVE MS SQL database market tool works in conjunction with another importer I had designed
EVEsqlimporter.
For the given reaction materials indices table, these are materials associated with the EVE reaction materials table, an average price is computed for the given top 10 high and low orders of the given material's market buy sell orders, also filter's for order movement. Will make updates later differentiating by region, and may increase volume movement threshold so as to throw out obvious order's that were obviously well off from a movement price average. At present this also discards old orders beyond 90 days or those of elapsed duration, based upon both date of order input and batch date entry of orders furnished at such time.
Computed average indices are imported to the reactionmaterialsavgindex table. This table is automatically created here for you. Just be sure to refresh your database through Management Studio
if checking for verification.
Updates 6/12/12:
Materials Reaction Average indices python script
-Computed averages are now differentiated by regionID (regional markets), key added for this on table. Also added standard deviation column. Provided filters for consideration of price order. Where, for instance,if standard deviation / computed mean > PCNT_STD_DV in script, then maximal price order is discarded and recursively averages are recomputed until standard deviation and mean fall within the given relation threshold provided, yet another tool aimed at filtering obviously high ball or low balled orders probabilistically least likely to filled in terms of buy or sell respectively. Added DISTINCT on query to ensure repetitions of orderID are not included (because of orders, for instance, appearing over any number of batch entry dates).
Generate items reprocessing estimated value python script
Lazy tonight will finish this up tomorrow for fetchall() or if you download feel free to update, just
need to fetch the rows and grab tablevalues. Will probably update this to write to dictionary.
At some point probably going to develop a Gui for all this so that a table populates this way.
If you are new to SQL I used a table JOIN method to cross link the data from various tables.
I also used an aggregate function SUM and had to use an aggregate GROUP BY clause so as
to be able to ensure that aggregate SUM s were applied by typeID and not every row value
irrespective of typeID. Also I had to link materialtypeID (this is the reprocessing material ID...for example, Tritanium, Mexallon, and so forth), with the computed average indices table for such materialIDs, once having done this, and having computed the reprocessing/manufacture materialIDs quantities with the average pricing, I summed these values grouped by itemID.
6/13/12 updates:
fixed pricing list generate sql query line...should work now in script. Also, added regionID filter
when pulling regional reprocessing/manufacturing materials. Also creates an 'items pricing' txt file
which includes line itemization of both item name and pricing estimation.
6/16/12 updates:
At present sort of exploring ideas here. Working on graphical ends, some thoughts as of current. Created a data synchronizations tab, sort of inspired by cloud type synchronization ideas here, but at the moment have kept this restricted to user interface toggling. May update synchronizations in tracking market log files, also providing a list of loaded market log files?!
Some basic ideas that I'd like to work on:
Creating an Items Marketplace explorer. This is not unlike a folder/file directory explorer, but instead uses the data tree found in Eve's market. Key table for doing this is the marketplace groups table. So my idea here for code implementation is fairly simple: simple query for data export, and use a recursive function for building data tree. Then to create an data import implement for data tree widgets (as I've seen commonly referenced in Gui languages). The purpose of the explorer should be simple enough, like being able to create customizable query filter searches for any list of items, alongside, providing an additional search query tool. I've seen this at least in other related type apps likewise. The marketplace group table should make construction of data trees very easy once the recursive function is found for linking groupID and parentID s here. In SQL you could do something like joins, or inner joins, and some SQL platforms offer recursion (think MS SQL offers this) while some are non recursive. SQL varies from other programming languages in terms of feel especially if you are used to the sorts of procedural flexibilities that allowed you to loop in more abstract way over a given dynamic range of data. If you weren't so well versed in SQL, this can be tricky. More likely the SQL solution would be a fixed set of table joins that might cover many trees, but not all here. Exporting data to a procedural language like Python, Java, C++ and so forth allows you on the other hand to create tree probing methods (recursively, or abstractly enough here) that could be more difficult to program in SQL (supposedly SQL means Set Query Language by the way).
Would also like to implement profiles that a user could save in so far as session work. Basically if a user want a customizable search query, the data that they were most interested in could be easily populated yielding an number of items at once...without having to tab through query drop down boxes, or key search for any of these items in a big list otherwise.
A open query box popup (simple), so that a user can input a custom query for execution...
Query fields for refined search (sort of open in thought and related direction here)...
Possible future things: Manufacturing calcs and the like. I once tried a fitting calcs idea, but I never completed it...sort of tried to create this fittings optimization algorithm...might try working on it again, depending.
Sticking with python's already packaged Tk here for gui writing, one since in window's it looks decent enough, and two I hadn't need go through a bunch of binding layer's to program a Java gui that basically looks about the same...Java seems like it could be nicer on some ends, but basically Tk provides enough for this project anyways, and then if you wanted to use python to program a Java app, you are actually running a python 2.5 version, or you could technically run using exec() type commands in higher version's of python Jython program's but not sure if it were at all possible to pass instanced data under jython compiler execution. Gtk in windows isn't good by the way, or at least they have old windows binaries going back to 2009 :) ...Tk is more up to date, and Tk is easy to work in the non visual approach, especially in Python. Seems a little less sophisticated (but actually a little easier) then Gtk but decent enough. Tk isn't so nice looking in Linux by the way (at least Ubuntu...so I highly recommend Gtk or other alternatives for gui app development)..data tree widget's in Gtk are a bit more technical on the implementation side of Gtk relative to Tk also. :)
To implement importer fully, you should have the pyodbc module. A c bindings driver that interfaces that provides pythonic interface commands for ms sql.
At the moment its still needs more work, you just need to set up at the program's outset a directory path to Eve market logs folder. There are three main table types where data is sent: reactionmaterialsindex, itemsindex, and myorders. Also you'll need to make sure that you enter correctly your sql server database information, alongside user id, password inputted. I may eventually get around to creating a windows based executable but for now you'll need to do this manually.
Dependencies:
python 2.7 + (works with 3.2)
pyodbc
MS Sql database2012 (hadn't checked other versions)
Eve fansite kit
Evesqlimporterscript
Windows installation procedure:
Download and install the above program dependencies. You'll obviously need to install python2.7 or 3.2 before you install pyodbc. Just grab the appropriate pyodbc module based upon your system and python version. Then install based upon your system, the MS Sql database 2012 with tools option. Then grab the fansite kit (just need Inferno_1.0_70633_db.zip).
Once you've installed MS Sql 2012, run Microsoft SQL Server Management Studio. Firstly I recommend in Object Explorer (left hand side of Studio window) right mouse button clicking on 'Databases' , here select 'New Databases' and then input in the Database field name 'Eve'. Press Okay. Next for the pyodbc driver you'll probably want to have SQL authentication enabled, so you'll need to enable this authentication by selecting in the Object Explorer, your Server Name, mine is for example, CHRISTOPHER-PC\SQLEXPRESS, it is the top most object item in the explorer's data tree structure. Right mouse click on this, and select 'Properties'. Then under 'Select a page' (left hand side of pop up dialog), go ahead and select, 'Sql Server and Windows Authentication Mode'.
Next you can create your Sql authentication based user id, by selecting in Object Explorer, YourServerName > Security > Logins. Right click on 'Logins' and select 'New Login'. Under 'general' (Select a page tab) of your 'New Login' dialog, select 'Sql Server Authentication'. Then enter your 'Login name' in the provided field, make note of this, you'll need to add this information to the python script that I have furnished. While there are many options under the 'Select a page' (left side of dialog, under 'Server Roles', 'User mapping', and 'Securables'. By default granting permission and check marking all fields for 'Server Roles' and selecting your 'Eve' database, should grant you necessary permissions for your userid. Make sure you have entered a password. This has a minimum characters requirement default, so make sure this is at 15 alpanums in length (at least I haven't been able to input anything less).
Next using a favorite text editor or using Server management Studio direct, copy and paste the ebs_DATADUMP sql query data into a new query. On Server Management Studio, you can select New Query, making sure that YourServerName > Databases > Eve is selected, and then paste the sql query code into the new query window that should be populated, or open the ebs_DATADUMP file directly from Server Management Studio. Then press execute. If all goes well should have new tables (with no data) furnished on your new Eve database.
Next you'll need to restore the backup database file for Eve. You can do this from Object Explorer, selecting YourServerName > Databases and right clicking Databases, you can select 'Restore Database..' I've found that owing to the silliness of the file finder dialog here to have the DATADUMP201205212008.bak (or related file depending on what version of Eve is presently current) having this setup in a convenient locations like 'C:\Users' by default. By default under the 'general' tab, select under 'Source' the 'Device' radio button. To this right of the now blank field under this radio button, select the '...' button. To the right of the presently blank 'Backup media' text field, select 'Add'. Then go to your source directory for the .bak file using the pop up directory tree explorer. Your backup file should be present in the file field. Select this, press Okay, and then push Okay again on the 'Select Backup Devices' dialog. You should now have the .bak file showing on your 'Restore Database' dialog now. Under 'Select a page' of the 'Restore Database' dialog, you should select 'options'. Then select 'Overwrite the existing database (WITH REPLACE)' and then select 'Preserve the replication settings (WITH KEEP_REPLICATION)'. Push 'Okay' on 'Restore Database' dialog here. If all goes well you should see a confirmation that your database has been restored. You can check for data in this database, by selecting in Object Explorer,
YourServerName > Databases > Eve > Tables and then selecting any one of the given 'dbo.' tables shown. You can right click on the table, and select 'Select top 1000 rows' . This will automatically generate a query for you populating database information for the given table. I highly recommend that you have some background in SQL, so that you can create your own custom queries likewise. Any decent book like SQL Visual Quick Start guide by Peach Pit Press publication for instance, can be an excellent start for learning some basics to query language programming. I also recommend some requisite knowledge of python here to aid you likewise.
Now that you have data in the database, using your typical market export methods in game, and presuming you have something in marketlogs folder to import into the database. You'll just need to furnish your server info, userid, and password at the 'cnxn' variable of the Evesqlimporter script. And put the directory path to your Eve market logs folder at the 'dirpath' variable. After running the script you may need to close down the existing sql server socket connection manually to populate say added data in Server Management Studio. Apparently I've run into difficulties with more then one server socket connection utilizing pyodbc and server management studio at the same time. In this case, just close out the idle terminal, not the script.
Finally while I have a batch file move script, to place a file in a given backup destination, I haven't implemented this. You could do this on your own if you like, or move the files to a backup destination other then your present directory. I may add for convenience sake later a file sync script, that records and tracks a list of uploaded files into the given database, thus avoiding having to do anything further other then making sure that files added to the folder or import merged into the database once synchronization is enabled.
Updates 6/11/12:
Materials Reaction Average indices python script
EVE MS SQL database market tool works in conjunction with another importer I had designed
EVEsqlimporter.
For the given reaction materials indices table, these are materials associated with the EVE reaction materials table, an average price is computed for the given top 10 high and low orders of the given material's market buy sell orders, also filter's for order movement. Will make updates later differentiating by region, and may increase volume movement threshold so as to throw out obvious order's that were obviously well off from a movement price average. At present this also discards old orders beyond 90 days or those of elapsed duration, based upon both date of order input and batch date entry of orders furnished at such time.
Computed average indices are imported to the reactionmaterialsavgindex table. This table is automatically created here for you. Just be sure to refresh your database through Management Studio
if checking for verification.
Updates 6/12/12:
Materials Reaction Average indices python script
-Computed averages are now differentiated by regionID (regional markets), key added for this on table. Also added standard deviation column. Provided filters for consideration of price order. Where, for instance,if standard deviation / computed mean > PCNT_STD_DV in script, then maximal price order is discarded and recursively averages are recomputed until standard deviation and mean fall within the given relation threshold provided, yet another tool aimed at filtering obviously high ball or low balled orders probabilistically least likely to filled in terms of buy or sell respectively. Added DISTINCT on query to ensure repetitions of orderID are not included (because of orders, for instance, appearing over any number of batch entry dates).
Generate items reprocessing estimated value python script
Lazy tonight will finish this up tomorrow for fetchall() or if you download feel free to update, just
need to fetch the rows and grab tablevalues. Will probably update this to write to dictionary.
At some point probably going to develop a Gui for all this so that a table populates this way.
If you are new to SQL I used a table JOIN method to cross link the data from various tables.
I also used an aggregate function SUM and had to use an aggregate GROUP BY clause so as
to be able to ensure that aggregate SUM s were applied by typeID and not every row value
irrespective of typeID. Also I had to link materialtypeID (this is the reprocessing material ID...for example, Tritanium, Mexallon, and so forth), with the computed average indices table for such materialIDs, once having done this, and having computed the reprocessing/manufacture materialIDs quantities with the average pricing, I summed these values grouped by itemID.
6/13/12 updates:
fixed pricing list generate sql query line...should work now in script. Also, added regionID filter
when pulling regional reprocessing/manufacturing materials. Also creates an 'items pricing' txt file
which includes line itemization of both item name and pricing estimation.
6/16/12 updates:
At present sort of exploring ideas here. Working on graphical ends, some thoughts as of current. Created a data synchronizations tab, sort of inspired by cloud type synchronization ideas here, but at the moment have kept this restricted to user interface toggling. May update synchronizations in tracking market log files, also providing a list of loaded market log files?!
Some basic ideas that I'd like to work on:
Creating an Items Marketplace explorer. This is not unlike a folder/file directory explorer, but instead uses the data tree found in Eve's market. Key table for doing this is the marketplace groups table. So my idea here for code implementation is fairly simple: simple query for data export, and use a recursive function for building data tree. Then to create an data import implement for data tree widgets (as I've seen commonly referenced in Gui languages). The purpose of the explorer should be simple enough, like being able to create customizable query filter searches for any list of items, alongside, providing an additional search query tool. I've seen this at least in other related type apps likewise. The marketplace group table should make construction of data trees very easy once the recursive function is found for linking groupID and parentID s here. In SQL you could do something like joins, or inner joins, and some SQL platforms offer recursion (think MS SQL offers this) while some are non recursive. SQL varies from other programming languages in terms of feel especially if you are used to the sorts of procedural flexibilities that allowed you to loop in more abstract way over a given dynamic range of data. If you weren't so well versed in SQL, this can be tricky. More likely the SQL solution would be a fixed set of table joins that might cover many trees, but not all here. Exporting data to a procedural language like Python, Java, C++ and so forth allows you on the other hand to create tree probing methods (recursively, or abstractly enough here) that could be more difficult to program in SQL (supposedly SQL means Set Query Language by the way).
Would also like to implement profiles that a user could save in so far as session work. Basically if a user want a customizable search query, the data that they were most interested in could be easily populated yielding an number of items at once...without having to tab through query drop down boxes, or key search for any of these items in a big list otherwise.
A open query box popup (simple), so that a user can input a custom query for execution...
Query fields for refined search (sort of open in thought and related direction here)...
Possible future things: Manufacturing calcs and the like. I once tried a fitting calcs idea, but I never completed it...sort of tried to create this fittings optimization algorithm...might try working on it again, depending.
Sticking with python's already packaged Tk here for gui writing, one since in window's it looks decent enough, and two I hadn't need go through a bunch of binding layer's to program a Java gui that basically looks about the same...Java seems like it could be nicer on some ends, but basically Tk provides enough for this project anyways, and then if you wanted to use python to program a Java app, you are actually running a python 2.5 version, or you could technically run using exec() type commands in higher version's of python Jython program's but not sure if it were at all possible to pass instanced data under jython compiler execution. Gtk in windows isn't good by the way, or at least they have old windows binaries going back to 2009 :) ...Tk is more up to date, and Tk is easy to work in the non visual approach, especially in Python. Seems a little less sophisticated (but actually a little easier) then Gtk but decent enough. Tk isn't so nice looking in Linux by the way (at least Ubuntu...so I highly recommend Gtk or other alternatives for gui app development)..data tree widget's in Gtk are a bit more technical on the implementation side of Gtk relative to Tk also. :)
No comments:
Post a Comment