| View previous topic :: View next topic |
| Author |
Message |
Styxx42
Joined: 08 Aug 2009
Posts: 8
|
 Posted: Wed Jul 28, 2010 18:26 Post subject: Installed and Running....NOW WHAT? |
 |
|
Hello NWNX community.
I don't have the vocabulary to do proper searches or even ask the right questions on this forum. I have been lurking for some time on these forums.
I read the white paper on how to get NWNX running, and did get it running successfully. But then was lost because of my massive ignorance on SQL and to be frank the NWN default Database. I didn't have the foggiest idea what to do next.
I have no scripting skills, and my database skills are very weak other then some very basic schooling and using what the good people at the NWN forums give and teach.
I do have the ability to learn as well as the real urge to better myself. I am in the IT industry and have been for 13 years now. But my job is more Windows Servers, WiFi networks, workstations and apps that I support. My job has never provided the impetus to pursue databases.
I am not hosting a public server, I don't aspire to. I want to start learning and I think NWNX will provide the fuel and the reason for this.
Long term goal is to host a server (which I already do using the a save file that just grows too big and is disruptive to the few ongoing quests) for a very small group of friends, where we can make changes to the PW(looking at some PW BAse modules on the Vault to start with and go from there) and not loose our progress.
So the crux of this long winded post is.
TOTAL newbie. Am I right in reading an SQL beginners book?
learning about the proper setup of a database(which I have a loose understanding) and how to run queries against this Database learning in turn how to use the "addons" that other community members have already uploaded.
I believe because I am in a closed learning environment I have an edge. I need to learn the how and what as they relate to NWN and NWNX and the different...Queries like save location on log out or rest. Store hitpoints. etc
I think that to get started with this I need to read a how to for beginners on SQL. I think that will get me the footing that I need to start using this forum intelligently and then the tools on the NWNX site it to begin using NWNX in earnest.
If not then what do you suggest?
Thanks.
I will await direction while googling the net for a suggested reading of SQL. |
|
| Back to top |
|
 |
Zebranky
Joined: 04 Jun 2006
Posts: 415
|
| Posted: Wed Jul 28, 2010 22:33 Post subject: |
|
|
With NWNX, it's often unnecessary to write custom queries. The Get/SetPersistent* functions provided in aps_include abstract that away, and you can use those for many purposes. For example, you could store the tag of the player's last area in a persistent string, and load that on login. Many systems only use that functionality.
Of course, if you want to learn SQL, that's an option too. The persistent location code I'm most familiar with does use custom SQL. I'd be happy to send that along if you'd like an example to look at.
I like learning from examples; I wouldn't get much out of a book as a raw newbie. Your mileage may vary. I did pick up MySQL Cookbook by Paul Dubois when I was in the beginner-intermediate zone, and I can recommend it, but it's more of a reference text than a way to learn from scratch.
_________________
Win32 SVN builds: http://www.mercuric.net/nwn/nwnx/
<Fluffy-Kooshy> NWNx plugin is to this as nuclear warheads are to getting rid of fire ants.
<ThriWork> whenever I hear nwn extender, I think what does NWN need a penis extender for? |
|
| Back to top |
|
|
PlasmaJohn
Joined: 04 Mar 2005
Posts: 70
Location: The Garage
|
| Posted: Wed Jul 28, 2010 23:28 Post subject: |
|
|
| The Get/SetPersistent wrappers are good if your data is atomic (doesn't depend on other data). I've seen a p-chest implementation via these calls that was a complete disaster. When what you're storing becomes complicated you really do need to break down and figure out SQL. |
|
| Back to top |
|
|
Styxx42
Joined: 08 Aug 2009
Posts: 8
|
| Posted: Wed Jul 28, 2010 23:49 Post subject: |
|
|
Zebranky
That would be great if you could send them to me.
I do learn way faster by example.
I have seen the APS referenced many times. Can anyone clear up or point to me where I can read about it. What is it? Is it an extension of Bio ware database? I wouldn't mind taking a read\look at that either. (mental note Check vault for anything APS related) SCRATCH THAT. The internet is wonderful http://www.nwnx.org/index.php?id=doc_odbc
Right here on this very website.
I was looking at the events in a couple PW modules that are hosted and see a bunch of lines in there that look like they reference SQL and I read how awesome NWNX is, so it is not that I want to learn SQL I want to learn NWNX to be honest.
I got NWNX on my windows box running so easy I had hoped there as a preconfigured system for a persistent world in it as a base to work from so to speak. But I found out I didn't know how to figure out if there was.
Thanks for your assistant.
Last edited by Styxx42 on Thu Jul 29, 2010 3:18; edited 2 times in total |
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Thu Jul 29, 2010 1:14 Post subject: |
|
|
Well, learning how databases work is probably the most important thing and the first step, since SQL is really just a very simple language that lets you access the data inside a database. Here's a quick "nothing to 100%" tutorial I've adapted.
So... a database is a collection of related tables. A table is a collection of records. And a record is a bunch of data about some identifiable thing. For example: a person, a place, a bank transaction, a forum post. Let's use the forum as an example.
Our first table is "users". This table is used to store information about each user. We have username, email address, join date and signature. Let's leave it at that for now - there's more, but it's not important. We also need what's called a primary key, this is some data which is guaranteed to be unique and unchanging for each user. The "username" field would seem to suffice, but consider, what if later on we want to change someone's username? There's a better way. If we give each person a unique number, calling it "uid" (short for user ID), it will suffice as a primary key. We might as well use the "auto increment" feature of the database system, this automatically numbers entries sequentially.
The resulting SQL code for creating such a table:
| Code: | CREATE TABLE users (
uid INT(11) AUTO_INCREMENT,
username VARCHAR(64),
email VARCHAR(64),
joined TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
signature TEXT,
PRIMARY KEY(uid)
) ENGINE=InnoDB; |
To quickly go over what all that means: "INT" is for "integer", "VARCHAR" is "variable-length character string", "TEXT" is for text of any length (there is a limit, but it's huge) and "TIMESTAMP" is a date/time. The numbers in brackets are the length of the field, so INT(11) is a number with no more than 11 digits, and VARCHAR(64) is some text at most 64 characters long. At the end we specify which field is to be used as a primary key. DEFAULT is used to specify a default value, this can be a string or number, such as 'sample' (WITH quotes), or an expression like CURRENT_TIMESTAMP, which unsurprisingly inserts a value appropriate to "now". Finally, ENGINE=InnoDB just tells it to use "InnoDB", which is a robust storage engine. Don't worry too much about that, just include it.
We've got a table of users. Now for a table of posts. Each post has a title, some body text, a post date and an author. Here is where primary keys come in handy: we can make a reference to another table's primary key by simply having a field of the same type (so INT(11) in a second table would be able to reference users.uid). But to ensure the table is sound, we can introduce a constraint like so.
| Code: | CREATE TABLE posts (
pid INT(11) AUTO_INCREMENT,
title VARCHAR(255),
body TEXT,
date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
author INT(11),
PRIMARY KEY (pid),
FOREIGN KEY (author) REFERENCES users(uid) ON DELETE SET NULL
) ENGINE=InnoDB; |
The new line simply says that 'author' is a foreign key, that is, it is linked to another table. It then describes which table/field it is linked to, and what to do if the other field is deleted. So, in English, "author should only contain the values in the field uid in the table users, and if a user is deleted, any post whose author is that user should have its author field set to NULL". NULL is a catch-all "nothing" value, which in this case could mean "anonymous".
Okay, that'll do. We have two tables, and we've already gone over everything you're likely to use. One table references another to link records in one table to those in another. You can have any number of these "one-to-many" references (one user can have many posts), and when you should use them typically takes a "follow your nose" strategy. While the SQL syntax for creating a table may seem a little confusing, it doesn't really get any more complicated than what you see above. You can create as many tables as you need, with as many references as you need, in exactly the same way as above.
Now let's insert some data.
| Code: | | INSERT INTO users(username, email) VALUES ('Fred', 'fred@example.com'); |
It's fairly self-explanatory syntax. In general the syntax is INSERT INTO table(field1, field2, ..., fieldn) VALUES ('value1', 'value2', ..., 'valuen');. Note that we don't give anything for uid, joined or signature. Because of AUTO_INCREMENT, the database sorts out "uid" for us. Thanks to DEFAULT CURRENT_TIMESTAMP, it automatically fills out "joined" as well. And "signature" defaults to NULL - the user can create a signature later or do without one.
We can keep adding users if we want, but that's boring. Fred's the first user, so his uid will be 1. Let's have him post something instead.
| Code: | | INSERT INTO posts(title, body, author) VALUES ('Post Title', 'Something interesting to talk about.', '1'); |
Okay, we have a user and a post. Whoopee. Let's check out all posts in the table.
| Code: | | SELECT pid, title, body, date, author FROM posts; |
For short, we could also have used *, meaning all fields, but the advantage of saying which fields we want explicitly is that we don't break anything by adding a new field. As the table is, the following all do the same thing:
| Code: | SELECT pid, title, body, date, author FROM posts;
SELECT posts.pid, posts.title, posts.body, posts.date, posts.author FROM posts;
SELECT * FROM posts;
SELECT posts.* FROM posts;
SELECT p.pid, p.title, p.body, p.date, p.author FROM posts AS p; |
The result is something like this:
| Code: | +-----+------------+--------------------------------------+---------------------+--------+
| pid | title | body | date | author |
+-----+------------+--------------------------------------+---------------------+--------+
| 1 | Post Title | Something interesting to talk about. | 2010-07-28 23:18:15 | 1 |
+-----+------------+--------------------------------------+---------------------+--------+ |
So that's basic selection. But there's a slight problem. We have no idea who the author actually is, only that their ID is 1. Here's one way we can find out.
| Code: | | SELECT * FROM users WHERE uid='1'; |
The WHERE here narrows down our search. You can be more specific, for example, WHERE name LIKE '_r%' AND joined < CURRENT_TIMESTAMP. (LIKE does a case-insensitive search, where _ is any one character, and % is 0 or more characters, so the above matches names where the second character is r or R, and anyone who joined before this instant.) But we'd really like to select them all in one go. To do this, there's a JOIN feature.
| Code: | SELECT posts.title, posts.body, posts.date, users.username, users.signature
FROM posts
LEFT JOIN users ON posts.author=users.uid; |
Broken onto several lines for readability. Here we're getting data from both tables at once. LEFT JOIN ... ON ... tells the database how to connect the tables together (in this case, join with the user table, making sure that author and uid are the same). The result is as expected. Note that LEFT JOIN means "take everything from the left table, and anything from the right table that is related". This means every matching row in the table on the left side of the join (posts) is used, and if anything in the right table (users) matches, it is used too, but otherwise we get a bunch of NULLs. It's subtle, but we'll see what it really means later.
That leaves two more tools: UPDATE and DELETE. Let's start with DELETE, removing Fred to see how post 1 in the posts table deals with losing its author.
| Code: | | DELETE FROM users WHERE uid='1'; |
As with SELECT, the WHERE condition for DELETE can be any comparison, so you can for example delete all users who joined more than a year ago, say. DELETE is similar in syntax to SELECT, except you can only do one table at a time.
Okay, so what do we have?
| Code: | | SELECT * FROM users; |
Result: Empty set. Fred is lost and gone.
| Code: | | SELECT * FROM posts; |
The result here is more interesting.
| Code: | +-----+------------+--------------------------------------+---------------------+--------+
| pid | title | body | date | author |
+-----+------------+--------------------------------------+---------------------+--------+
| 1 | Post Title | Something interesting to talk about. | 2010-07-28 23:18:15 | NULL |
+-----+------------+--------------------------------------+---------------------+--------+ |
We ended up with a NULL, despite not touching the posts table. That's the ON DELETE SET NULL that we set up for author kicking in: author 1 was deleted so post 1 is now an anonymous post. If we run
| Code: | SELECT posts.title, posts.body, posts.date, users.username, users.signature
FROM posts
LEFT JOIN users ON posts.author=users.uid; |
again, we find that username and signature are both NULL too.
Okay, finally, UPDATE. It should be obvious from the name what it does. Here's the syntax.
| Code: | | UPDATE tablename SET field1='value1', field2='value2', ..., fieldn='valuen' WHERE condition; |
e.g.
| Code: | | UPDATE posts SET title='Revised title' WHERE pid='1'; |
That should be all the tools you need. Get MySQL or SQLite set up, run some commands and try it out. Experiment! Note that with SQLite, deleting a row and having another table set its field to NULL might not work, I'm not sure.
With NWNX, SQLite should "just work" without any setup at all. All of the above commands should work using the SQLExecDirect function from the aps_include script. For the SELECT queries, you also need to use SQLFetch and SQLGetData to actually get any data out. |
|
| Back to top |
|
|
Styxx42
Joined: 08 Aug 2009
Posts: 8
|
| Posted: Thu Jul 29, 2010 1:45 Post subject: |
|
|
Having read that one page, http://www.nwnx.org/index.php?id=doc_odbc
it is coming together.
I think my last go at this I put the cart before the horse.
I found the NWNX link on the vault, read quickly what it can do, Got frothing mad with amazement and excitement, went to the download page, got it, read how to get it going, got it running and ran straight into a wall when I was looking sideways at the beautiful NWNX server I had running.
It knocked me senseless.
PlasmaJohn I am confused by your statement. Wrapper. What is that?
Is that one of the Functions in APS that redirects queries back to the SQL then into the database?
Can you elaborate? I think you mean that I do need to learn SQL more intimately . I agree with you. But won't with my limited needs stated above, with me starting with the basics of APS get me on the path to SQL glory and enlightenment by having to creating the tables, the database and starting to work with the queries? The one thing i do have in spades is Time.
With just that little bit of help from Zebranky and a small pointer to APS I have a good starting point. I was able to find the ASP information page that has the demo modules and examples I was looking for.
Functions, Strings, tables. IT is all coming back to me in a very loose sense.
If you all don't mind I am going to use this post to track how and what I find and any questions, successes and failures I have from a total beginners point of view.
Thanks for the help so far people. I am already on the road towards my not so Lonely Mountain. When just this very morning the best I had was my elbows deep in suds cleaning up after the dwarves in my kitchen. I feel like I am beginning a journey to adventure.
I can't wait to get my first restart of my server and have my character still out int he woods. That will be my first goal I think. Hopefully not too big. Let me know if I am over shooting with this.
Thanks
Last edited by Styxx42 on Fri Jul 30, 2010 1:54; edited 2 times in total |
|
| Back to top |
|
|
Styxx42
Joined: 08 Aug 2009
Posts: 8
|
| Posted: Thu Jul 29, 2010 3:13 Post subject: |
|
|
Fireboar
That post is fabulous.
I went blind in one eye while reading it but I am certain I will regain my sight with some rest.
Thanks for the brush up and tutelage.
Question.
Where do you get a list of expressions like CURRENT_TIMESTAMP?
Are they database specific (MySql, or SQLLIGHT) or general to all Dbases or last of all unique to NWN?
regards |
|
| Back to top |
|
|
Fireboar
Joined: 17 Feb 2008
Posts: 323
|
| Posted: Thu Jul 29, 2010 11:56 Post subject: |
|
|
| There are actually hundreds of expressions. For MySQL, they can be found here, and most of the ones supported by MySQL are supported by other databases too (though possibly not SQLite). The most common ones are CURRENT_TIMESTAMP or UNIX_TIMESTAMP (UNIX_TIMESTAMP lets you store a time as an integer: number of seconds since 12am on 1/1/1970) and NULL. I very occasionally use IF and CONCAT, but not often. I suggest if you need an advanced bit of functionality, you simply Google it at the time. Since SQL is simply used for getting data, provided your database makes sensible use of relationships and primary keys, you rarely if ever need to deviate from the form of the examples above. |
|
| Back to top |
|
|
Styxx42
Joined: 08 Aug 2009
Posts: 8
|
| Posted: Thu Jul 29, 2010 15:02 Post subject: |
|
|
Thanks to both of you for your assistance.
I believe I have enough to really start to learn.
Like I said, last time I rushed it. I need to take my time, ask questions that are not easily found by doing a search or some reading.
I am going to dig in now and see what I can learn by reading and looking at other modules already setup with NWNX. That should help me get on my way.
I am indebted to you both. |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
