Bashful asks for some SQLite help

[4ggr35510n]

Greetings!

I'm trying to create my very own game in Autoit. During my work, I realized I need to use SQLite functions to store and read data from database.

I've read helpfiles, examples, SQLite Syntax Help, some tutorial on the internet, but I feel I'm getting more and more confused instead of becoming wiser on this subject...

I've gathered some info about using sqlite, but still...

I know there is something like "PRIMARY KEY" and also an "INDEX" which can be an attributed to a column... Does "indexing" increase speed of searching? Can I index two columns? If so, which index will become more "important"?

Suppose I need to store/read some integer-based coordinate system. I have some data ascribed to an unique X and Y position. X and Y doesn't change, but data does changes, often and a lot. While storing - how can I check is there any record ascribed to X and Y position (represented by first two columns) AND if exists - UPDATE, if not exists - INSERT? Should I use TRIGGER (how?)? Or maybe INSERT OR REPLACE (how?)? While reading - what way will be most efficient, when I need to check both X and Y columns?

I'm terrible sorry for such inaccurate description and no code-example... I'm too noobish about SQLite to make it any code... I've read about some basics query commands, but there are almost none examples how to properly use them...

Still, hoping for ur forbearance and kindness :]

Best regards, appreciating any help

4gr

Edited August 10, 2010 by 4ggr35510n

[jchd]

Let me give you some generic hints.

First you should read some SQL primer found on the web.

First ex-aequo you should download and learn how to use one of the SQLite DB management tool available. I highly recommend SQLite Expert, which comes in free and Pro versions.

The two steps above will greatly help you practice simple and more advanced exercises in a very convenient and efficient way.

You should then read basics about DB design. SQL essentially wraps around set-theoretic operations and concepts. As a consequence, the most efficient DB design for a given problem is generally 90° (if not 180°) away from the approach you would use to implement the solution in any imperative language. Never forget as well that you want your DB to be efficient and expandable to accomodate changes and extensions of your software over time.

When it comes to storing 2D or 3D data, the RTree extension is probably a good choice. Think of an SQLite extension as a plugin which offers new functions or changes native functions of SQLite. Even writing custom extensions isn't hard if you're moderately proficient in C.

Don't get the fear! SQL isn't that hard anyway and your application isn't likely to require superioir academic knowledge.

To answer your insert or replace question, it all depends on what is your primary key for this table. Here's is an example schema where you can insert X, Y, attribute rows without ever having to check if a given XY point needs insert or update. I don't say this is what you need in all case, it only shows how this is possible.

CREATE TABLE [test] ( [X] INTEGER,  [Y] INTEGER,  [atb] CHAR(20),  CONSTRAINT [] PRIMARY KEY ([X], [Y]) ON CONFLICT REPLACE);insert into test values (1, 2, 'my comment');insert into test values (1, 2, 'my new comment');insert into test values (3, 2, 'my comment for this one');insert into test values (1, 2, 'no comment');insert into test values (1, 2, 'no new comment');insert into test (X, Y) values (3, 2);insert into test values (1, 2, 'my no new comment');RecNo rowid X Y atb    ------- ------ - - -----------------    1   6 3 2 (null)        2    7 1 2 my no new comment