Auto SQL update

[EvilPuncker]

I would like to suggest the addition of an auto update system for sql db updates, like:

• We start with db_version 0
  
• Something got modified in db so instead of devs adding a new .sql upgrade file (that noobs don't know a sh*t about, look at support and bugtracker for "favorite") they would add it to source and it would execute on server start and would change db_version to 1
  
• etc etc
  
• etc etc etc
  
• for those who knows a bit about coding, take a look at this as example, it is from opentibia emulator
  
• feel free to flame this thread, I don't care
  

It was just something that passed into my head after seeing all that topic/bugreports every time that a .sql upgrade goes out, it would be really nice IMO to have it implemented! but lets elaborate and give your point also (you guys are negative but whatever xD)

[Vengeance]

this will cause problems for any custom modifications done in the db or the server...better this things be how it is...or a new addon to just add sql to db..like the map cache will do I guess...

[xazax]

I think it is possible to create an optional step: importing the whole item_db.txt or item_db.sql to sql at startup. But I don't really see the point in of this feature.

What would be more useful, and still possible to implement is: to create a post commit hook, that monitors for db changes, and if item_db or mob_db changes, do automatic conversion, and a commit. This way the sql files would be 100% percent up to date all time.

[EvilPuncker]

xazax and others! I mean changes into main.sql xD

[xazax]

Oh, I see your point now

[EvilPuncker]

some issues that current system causes:

1. http://rathena.org/board/tracker/issue-6391-unknown-column-r16552/
   
2. http://rathena.org/board/tracker/issue-6355-sql-error-related-to-srccharcharc878/
   
3. http://rathena.org/board/tracker/issue-6364-db-error-unknown-column/
   
4. http://rathena.org/board/topic/68445-unknown-column-favorite-in-field-list/
   
5. http://rathena.org/board/topic/68407-regarding-about-lots-of-problem/#entry125272
   
6. some more I've missed
   

[xazax]

Yeah, as far as I can see, it would definitely involve giving the database some kind of version.

After that, there are 2 options:

* Hardcoded updates on startup

* Execute formatted .sql files from a folder on startup.

One must be able to disable this feature though, because it is a security hole, and you need to set less strict premissions to your server's mysql user. ( however I guess most user do not bother much with permission anyways )

[EvilPuncker]

One must be able to disable this feature though

indeed

[Lemongrass]

I would rather prefer, that the rAthena SVN version is merged into a file and this file is checked on startup and if there is a sql update file for that revision the server(s) should tell you at startup that you have to run these.

That would require to save two values though:

the last revision that was used

the current revision

So it can tell you all of the updates that have to be done to the database. But I would not run it automatically since you can never know if someone changes his database and these changes contradict to the newest updates.

[Vianna]

Then we would have to give privileges to alter tables to the MySQL account used by the server =(

The database itself could have a row somewhere telling its version, and then when a dev changed the tables in a way that they would have to be updated using upgrade_rXXXXX.sql, he would also increment a constant, let's say, MIN_SQL_DB_VERSION, in mmo.h. The upgrade_rXXXXX.sql file would also change the version number. Then, whenever a server started, it would compare the database's version with MIN_SQL_DB_VERSION and it would print a message saying that the tables were changed and that they have to be updated manually.

[EvilPuncker]

you guys make things look way so complicated when they are not =P

[Salepate]

It is true, the server's sql account should remain at minimal privilege, it is not its task to update SQL table, but to display a warning on start-up is a good idea though.