Jump to content
Sign in to follow this  

Bashful asks for some SQLite help

Recommended Posts


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


Edited by 4ggr35510n

Share this post

Link to post
Share on other sites

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 

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post

Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

  • Recently Browsing   0 members

    No registered users viewing this page.

  • Create New...