Creating Database Help

Ask for help regarding any technical issue or report any bug or OS independent issues.
Post Reply
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Creating Database Help

Post by badmiracle » 22 Mar 2011, 20:49

Can someone just explain better how to go about creating the database using SQLite?
Preparation of database file

The creation of a new database file is very easy. Just go to the directory where you want to create it and type:

sqlite3 mana.db < ${ManaServ-dir}/src/sql/sqlite/createTables.sql
Do I do this using sqlite3.exe or cmd prompt?

And if my manaserv folder is located on my desktop what would should {ManaServ-dir} be changed to?


Thanks in advance.

Reference: http://doc.manasource.org/database_installation
User avatar
Crush
TMW Adviser
TMW Adviser
Posts: 8046
Joined: 25 Aug 2005, 17:08
Location: Germany

Re: Creating Database Help

Post by Crush » 22 Mar 2011, 21:07

I usually put the sqlite3.exe into the manasource directory, start it and then enter .read src/sql/sqlite3/createTables.sql.
  • former Manasource Programmer
  • former TMW Pixel artist
  • NOT a game master

Please do not send me any inquiries regarding player accounts on TMW.


You might have heard a certain rumor about me. This rumor is completely false. You might also have heard the other rumor about me. This rumor is 100% accurate.
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 22 Mar 2011, 21:22

Okay so I did that, and put in:

sqlite3 mana.db < $.read src/sql/sqlite/createTables.sql

I also tried: sqlite3 mana.db < $.read/src/sql/sqlite/createTables.sql

and nothing happened. When I put a semi-colon ";" at the end I get an Error near "sqlite": syntax error
User avatar
Crush
TMW Adviser
TMW Adviser
Posts: 8046
Joined: 25 Aug 2005, 17:08
Location: Germany

Re: Creating Database Help

Post by Crush » 22 Mar 2011, 21:37

sqlite3 doesn't give you any success message when it works. Just an error message when it fails.

You can see that it worked when:

1. a file mana.db appears
2. you enter .tables and get a list of tables
  • former Manasource Programmer
  • former TMW Pixel artist
  • NOT a game master

Please do not send me any inquiries regarding player accounts on TMW.


You might have heard a certain rumor about me. This rumor is completely false. You might also have heard the other rumor about me. This rumor is 100% accurate.
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 22 Mar 2011, 21:40

It is not creating any mana.db file in the directory.

Can you give me the exact command to put in? or did I have it right.. :S
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 22 Mar 2011, 22:16

Im noticing that "mana.db" gets created when I run "accountserver.exe" (it closes right after)

Image


The mana.db file appears to be blank. I tried to use sqlite3.exe to import the tables into the file using the following command in sqlite3.exe (which is in my manaserv dir):

Code: Select all

sqlite3 mana.db < $.read src/sql/sqlite/createTables.sql
but I get no change..

Can someone tell me what I'm doing wrong please.


Thanks in advance.
User avatar
Crush
TMW Adviser
TMW Adviser
Posts: 8046
Joined: 25 Aug 2005, 17:08
Location: Germany

Re: Creating Database Help

Post by Crush » 22 Mar 2011, 23:15

Clipboard01.png
Clipboard01.png (33.87 KiB) Viewed 2102 times
  • former Manasource Programmer
  • former TMW Pixel artist
  • NOT a game master

Please do not send me any inquiries regarding player accounts on TMW.


You might have heard a certain rumor about me. This rumor is completely false. You might also have heard the other rumor about me. This rumor is 100% accurate.
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 22 Mar 2011, 23:17

Thank you.
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 23 Mar 2011, 14:25

Crush would you mind taking a screenshot of what you did in command prompt for "making a GM account" I can't seem to get that to work now either..

Your last post was really helpful, and this would be greatly appreciated.

Thanks,

-Adrian
User avatar
Crush
TMW Adviser
TMW Adviser
Posts: 8046
Joined: 25 Aug 2005, 17:08
Location: Germany

Re: Creating Database Help

Post by Crush » 23 Mar 2011, 16:00

makegm.png
makegm.png (90.96 KiB) Viewed 2077 times
The line .explain on is not required. it is just for seeing the column name headers in the output of select statements.

The select statements are not required either. I just used them to show the change made by the update command by requesting a list of all accounts and their access levels before and after the update.

I got only one account right now on my test server, but on a live server you would have - depending on how popular you are - a few hundred to several thousand accounts. So it is useful to get the access level of an individual account:

Code: Select all

SELECT level FROM mana_characters WHERE username="Adrian";
or a list of everyone you gave special privileges through an SQL command or through the @givepermission command ingame.

Code: Select all

SELECT username, level FROM mana_characters WHERE level > 1;
There is a lot more you can find out or do on Manaserv through SQL commands. When you are interested in learning some more advanced SQL wizardry I can recommend the website http://sqlzoo.net
  • former Manasource Programmer
  • former TMW Pixel artist
  • NOT a game master

Please do not send me any inquiries regarding player accounts on TMW.


You might have heard a certain rumor about me. This rumor is completely false. You might also have heard the other rumor about me. This rumor is 100% accurate.
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 23 Mar 2011, 16:20

Thanks for the prompt reply,
but crush I don't think im getting anything..
Attachments
zz.png
zz.png (14.93 KiB) Viewed 2074 times
badmiracle
Newly Registered User
Posts: 10
Joined: 22 Mar 2011, 20:43

Re: Creating Database Help

Post by badmiracle » 23 Mar 2011, 16:32

Is it possible that my "mana.db" file was built wrong?

I mean the formatting looks pretty messed up.. http://pastebin.com/GcCgfuc9
User avatar
Crush
TMW Adviser
TMW Adviser
Posts: 8046
Joined: 25 Aug 2005, 17:08
Location: Germany

Re: Creating Database Help

Post by Crush » 23 Mar 2011, 16:35

It seems like you have no accounts in the database yet. You have to create an account through the game client first. Start the accountserver, start the game client, connect to your server with the client, and click "Register" on the login page. Then you can promote this account from a normal player to an administrator account through an SQL command.

Your database file looks pretty normal. It's a database. It saves data so that it can be read efficiently by machines, not in a human-readable format.
  • former Manasource Programmer
  • former TMW Pixel artist
  • NOT a game master

Please do not send me any inquiries regarding player accounts on TMW.


You might have heard a certain rumor about me. This rumor is completely false. You might also have heard the other rumor about me. This rumor is 100% accurate.
Post Reply