I Want to be a DB editing freak !

[Wumpas]

Scorpius, TCS, Dev/noob, bud9wiezer, Myra . . . If you guys or out there or anyone that can shed light on this I got a question.

I am curious as to how to merge databases i dont think its quite as simple as source XX, source XX Ive tried this method but my pc speacker always screams at me for it with lots of errors.

I want to be an uber DB editing freak please explain how this works LOL I know that would probably take days to explain but if you or anyone could go over just some basic stuff it would be of great help.

I would love to get myras DB working with TCS's for the grids and such. If there is any DB editing threads i missed ( I used the search feature many times on this I just don't know what to type in the search field to get the best answers)

I know how to open *.sql files in notepad
I know how to save a *.txt as a *.sql file
I know how to source TCS database and his update.
I know how to use fqadmin 4.8 (get some errors but i can get to most of the tables)
I know how to get mysqlcc to work but i cant find that many tables in it

My real question is how do you take parts of one database and merge if with parts of another and so on. (Can this be done?)

Id love to understand the database as best i can if anyone has a list of all the tables (or a way i could generate my own) in 5.2 that would help out to.

Thank you for any help in advance

again dont feel im asking to learn this in one day beacuse as nice as that would be im not.

Im looking for any info I can if anyone can throw any info here and the tread gets enough to make a Tut out of wouldn't that be a welcome adition to the guides section of the forums. (Hell if i could get to understand all this stuff like the above mentioned demigods Id even offer to write the tutorial.)

Also If my questions about the inns and outs of the eqemu are getting wearing let me know. Im sorry its the Gnome in me I gota tinker with things. My curiosity is more a curse than a blessing some tell me

[haecz]

heheh, some flesh meat to the eqemu-staff, ey? ;)
think its great that u want to help the progress
of eqemu...
hope they throw ya a bone.. good luck to ya mate :)

[vbemos]

I think the best way to view the database is in mySQL, these are the basics, make sure you back your database up before you start, I don't want to be responsible for a destroyed db :shock: (to do this goto the \data folder in mysql and copy out the eq folder, you can paste it back if all goes wrong) **lol**

once your on it type USE eq; to select that db, or whatever you called it then SHOW TABLES; and it will list all the tables in the db, DESCRIBE <tablename>; will tell you what the table is made up of for example: mysql> DESCRIBE inventory;
+---------+-----------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+---------+-----------------------+------+-----+---------+-------+
| charid | int(10) unsigned | YES | | 0 | |
| slotid | smallint(6) unsigned | YES | MUL | 0 | |
| itemid | mediumint(7) unsigned | YES | | 0 | |
| charges | tinyint(3) unsigned | YES | | 0 | |
| color | int(11) unsigned | | | 0 | |
+---------+-----------------------+------+-----+---------+-------+
5 rows in set (0.10 sec) To view records in the db type SELECT <fields> FROM <table> WHERE <clause>;
Example: SELECT id, name, password, status FROM account WHERE status > 200;
Example: SELECT id, name, password, status FROM account WHERE name LIKE '%eq%'
Note that % is the wildcard in mySQL, to add a record type INSERT INTO <table> VALUES (<values>)
Example: INSERT INTO forage VALUES (0, 100, 1001, 1);
To change an Existing record do: UPDATE <table> SET <field>=<newvalue> WHERE <clause>;
Example: UPDATE accounts SET password='newpassword', status=200 WHERE name like '%eqemu%'; To delete a record type
DELETE FROM <table> WHERE <clause>;
Example: DELETE FROM zone_points WHERE id=101;
to Delete a database or table type:
DROP TABLE <tablename>; or DROP DATABASE <dbname>;
Example: DROP DATABASE eq; <Don't try it :P
to create a table do:CREATE TABLE <tablename>(<fieldname> <type>)
Example: CREATE TABLE people (name varchar(20), age int(3), dob date); to create a db type CREATE DATABASE <dbname>;
Example: CREATE DATABASE eq2;

to make a source file you need to run another program call mysqldump (in the same dir as mysql) type at a command prompt:mysqldump <db> <table>
Example: mysqldump eq, account; and to source it in as you know its source <path>;

How that was what you wanted?? :D

[Wumpas]

Thank you vbemos Its going to take me a while to digest this but it seems to be a step in the wright direction I had intended for the thread I thank you.

Im going to practice this stuff today and post and questions here as well

Thank you for the information helps alot

Exelent Resource

EDITED ----HOLY CRAP the database is just HUGE

P.S. that was a lot of work thank you again for giving that much time to help me out.

[Wumpas]

Ok ive been paging through and was wondering if there is any way i can find out what social means in npc_types table (is there a developers list that has definitions of tables and fields and what they do?

so far ive deduced that npc_types holds mob stats and spawn2 holds sets of mobs that are assigned to a grid ??? am i right with any of that???

If this is so how do i create my own spawn sets?

thank you in advance

[devn00b]

As i have said before, law of deminishing returns...

Combining db's = more work than the rewards.

[vbemos]

Most welcome for whats there... its only the basics, but I like to help where I can. The db is huge, alot of work must go into them and I am VERY thankful people share them and create them =)

Like /Dev/Noob says it would be alot of work to merge two+ dbs, but still if you want to do it **hehe**

**puzzle** I know I came here to do something and I can't remember now :? **sigh**, well, I'll remember when I log off... curse... come back.... **hmm**

[Wumpas]

Alas every development group needs its no man

I want to learn this anyway I know its going to be hard I may not be able to finish but damit ill learn from it and thats the whole reason Im drawn to this project !

[Wumpas]

TO Dev/noob
I am sorry if you read my above post before I edited it please don't take it to heart I was mad at myself for hitting a deer today I should not have let my anger seep into the forums. It was out of character and totaly uncalled for. I place myself at your blade.

Back to topic
I read that the following tables have been added to the database for 5.2.

eventlog/objects/zonelines/respawn_timer in groundspawns

I read this on a random thread I was reading someware I forget which one exactly but it wasn't the topic of the thread and was mentioned casuly.

What im asking is whare is the official place that tables added and field parameters are listed so that I can add the other tables I need to my database corectly. Does such a place exist or is it all
inside information with the major DB editors?

Is there a place in the source code I can look to to see all this info ive searched it but theres alot there, I barly scratched the surface.

Thank you for the help database gurus.

--Im prepared for rude awakenings as to the work I have ahead of me and I want to try anyway lets just assume I have years to work with and an unquiting attitude wich at the moment I do.--

It's the only way ill learn all this I gota start somewhare

[vbemos]

I don't think there is any resource, it would prob be in the world building section if there is. but its all mostly self explainitory (sp?), you mean a resource to say what everything in the db does?

[devn00b]

Now wait just one GD second.

when did i say no?

i said that it will be more work than the rewards intail.

Just because you dont like what i say you bash me (erased later), sounds to me like you have some issues.

You flame me if you like makes no difference to me. BECAUSE I HAVE CREATED A DATABASE, I HAVE CONTRIBUTED TO THE EQEMU COMMUNITY. What have you done?

If you wish to learn why not make your own database from scratch? Use packet collector to collect spawns from live, make the loot tables, merchant tables ect. Would probly be about the same amount of work.

[ZER0C00L73]

wow... little harsh given the apology in his above post, and the state he was in regarding the deer?

[devn00b]

Excuses are like assholes....

While im sure he didnt mean what he said, my point is still valid.

[Wumpas]

Yup your point is valid and thats why i put myself at your blade

I thank you for your critisims though I was hoping for an I forgive you I guess ill have to work my way back into your good graces

I have tried my hand at combining tcs myworld with myras spawn database today as an experiment and 6 hours latter its playable though I think i meissed somthing as all teh merchants tell me im not in the magitial class guild hehehe guess I need to edit something else in there Im going to dive back in tomorow

again My apologise dev/noob you have given alot and I havn't yet I was hoping to get my "Mut" database up today and see if anyone wanted it but as I said it still needs mondo work

If my apologise arn't enough I freely submit to your will You can change my avatar to somthing embarasing or what ever creative viletry you come up with I am prepared to be your B**h for as long as it takes to raise your faction to indifrent or alternativly higher.

IE --if you have any quests you need done like changing a value in 32,000 lines of database Ill get to work on it before you say jump.

P.S. About the deer it caused 1300 dollars in damages and all I have is basic insurance no extra coverage as im a lowly college student I was in a Very bad mood not to mention im still sad for the deer.

I wasn't using it as an excuse I was just explainining my mind set in my previous transgression so you would at least know why i fliped my lid. also I didnt bash you that bad anyway I just spouted off a few more lines on the no-man bit and now I realise you wernt saying no it just seemed that way and with your scary intimidating avatar (which by the way rules) it struck a disinant cord with me at the time.

behead me if you will my consious is once again clear.

[devn00b]

NP Wump. I hope when your database is playable it works ;) i might even have to take a look at it myself. ;).

[Wumpas]

Well I feel a bit more releaved heh

I learned alot about the database today and thanks to all the commands above by vbemos I acomplished alot for 1 day I think

Really all I did so far was set up a database that drops all the tables if they exist and starts clean and I used the db.sql for most of the creation strings I then went through TCSMyworld, Myras, 0.5.2-Update, latest dumpeditems and pasting them together with a TON of scrolling back and forth

Note: Also through in some fixes from the community like the object fix by Kroeg to fix the forge bag issues. (Before I release this im going to change all those values the right was ad delete the replace strings)

Im going to redue much of it tommorow and try to get my shopkeepers/bankers working My goal was a complete DB with Full shopkeepers from TCS as many spawns as I could get from Myras and movment to be added by me empty grid table (another fun project for me to do!)

Im not sure what tables could be causing merchants/bankers to tell me in not in the magician class guild any thoughts?

When I get to the point where I learned what I will from It ill try to post it if it is mostly functional

Also I am going to start editing zone points they are mostly corect or ballpark, but a few oddballs in the mix.

<note I have no idea how to post it as It is 32MB in size so far ! Ill zip it once I finish with it and see from there>

These are the tables I have so far am I missing any?

aa_timers
acount
altadv_vars
books
bugs
character_
character_backup
city_defence
class_skill
doors
eventlog
faction_list
faction_values
forage
grid
ground_spawns
guild_aliances
guild_controllers
guilds
hackers
items
lootdrop
lootdrop_entries
loottable
loottable_entries
merchantlist
name_filter
npc_faction
npc_faction-entries
npc_spells
npc_spells_entries
npc_types
object
object_contents
petitions
pets
player_corpses
player_corpses_backup
sharedbank
spawn2
spawnentry
spawngroup
start_zones
starting_items
trader
tradeskillrecipe
variables
zone
zone_points
zone_server
zone_state_dump
zonepoints_raw
zoneserver_auth


I think i got a basic handle on the tables and functions of them but How do i figure out what each field means? and in defining them what things like in(11) mean?

Thank you for the help

[vbemos]

Your magcian prob sounds like your spawning magicain trainers rather than merchants maybe :D

as for your question on field types they are loads, this seems to be a good defination: http://scibit.com/help/mascon/linux/wt_mysql_field_types.htm

(*whew* save myself some work there =p)

and the bracketed number dictates field size i.e. varchar(13) is a variable length string.. max length 13 =)

[Wumpas]

Thank you Vbemos ill look back through the spawns right quick
im using Myras spawns mabe she hasnt gotten any of her shopkeeppers functioning yet wich is fine. I am going to have fun trying to figure out how to fix it. Thank you for the help with the field definitions and im going to go through that link too it look very helpfull ! :D

[bud9weiser]

Wump, if you really want to learn the ins and outs of db editing jump on irc and join #npcmovdb and ill do what i can to help

[Wumpas]

Thanks Bud9wieser Im going to hop on there tommorow after work The help would be great then I can help you and tcs mabe if i learn enough. I didn't want to try jumping into helping you guys untill i learned my way around but since your willing to take a noob under the wing ill gladly join in !! :D (im curently trying to help Omper with race gender texture combinations) tommorows all database thoug :wink: My work hours are flexable ill probably bew back around 4 mabe hehe well see how it goes. if your not on then dont worry about it ill get on and check in and out every so often from now on.

sorry omper ill still run your script at night and get one zone a day ready for you guys they take forever to run i devoted most of my cpu cycles today

[bud9weiser]

Wump, i apologize for not being on yesterday, i ended up working really late, i should be around the rest of the week

[flyrken]

Well, looks like you are on your way to being a db editting freak, because you joined the right project.

:lol:
LOL


EOF

[Lurker_005]

vbemos posted some nice started examples for manipulating hte DB. Always learn the commands, it makes life so much easier as you learn and try to do more with them.

I also suggest you check out some of the tools posted in http://www.everquestserver.com/forums/viewtopic.php?t=11105

Both for file editing/comparing, and the MYsql GUI apps that make viewing data SOOOOO much nicer. Note you still have to know the commands vbemos posted!

As far as merging Databases... that is really a tough on to do bucause there are so many issues involved. At the very least I imaging it would take wrighting an external application that would take all the data that is referanced by ID elsewhere in the DB, and combine it. Then go back and insert that into the existing DB.

This applies to spawns, loot, factions, vendors.... vendors are fairly easy, Get all the items for a given vendor, plus the vendor name, and zone. Then after you had merged the spawns you would have to look up the ID of the spawn that originally had it (based on name and zone) and apply the items to that vendor. But you also have to make sure you didn't duplicate items on a vendor... so you se even for a "simple" example there is a lot to do.