SQLite Index question

[bule]

I have a question regarding creating indexes:

I have the following in my Project's On Startup:

base=data.."\\dbase.mrp";
db1 = SQLite.Open(base);

SQLite.Query(db1, "CREATE TABLE obj(id integer primary key, naziv text, adresa text, tel text, akt integer);", nil);
SQLite.Query(db1, "CREATE TABLE art(id integer primary key, naziv text, gru integer, akt integer);", nil);

SQLite.Query(db1, "CREATE INDEX objid ON obj (id);");
SQLite.Query(db1, "CREATE INDEX artid ON art (id);");

While the tables will be created only once (first run), will the indexes be recreated
each time the application is ran, or as with the tables, only first time?

I am asking this bacuse I saw the syntax for SQLite v3 and the syntax is:

CREATE [UNIQUE] INDEX [IF NOT EXISTS] [database-name .] index-name ON table-name ( column-name [, column-name]* )

http://www.sqlite.org/lang_createindex.html

(The [IF NOT EXISTS] is not supported by SQLite 2.x)

[TJ_Tigger]

This is from the page you posted

The exact text of each CREATE INDEX statement is stored in the sqlite_master or sqlite_temp_master table, depending on whether the table being indexed is temporary. Every time the database is opened, all CREATE INDEX statements are read from the sqlite_master table and used to regenerate SQLite's internal representation of the index layout.

If the optional IF NOT EXISTS clause is present and another index with the same name aleady exists, then this command becomes a no-op.

It sounds like when you open the database it will regenerate the index. When generating the index from AMS I wonder if you would want to add a check that looks at the sqlit_master table to see if the index already exists and not perform the create index if it has already been done.

One question I have is do you receive an error when it tries to recreate an index that already exists? I know if i try to create a table that already exists using SQLite.Query it outputs an error to the debug and does not actually recreate the table. I wonder if the index does the same. (I usually use Edit->Preferences : Show Debug ...)

Tigg

[bule]

[29]: SQLite.Query(db1, "CREATE TABLE pro(id integer primary key, iznos integer, kon integer, obj integer, dat integer);", nil);
TRACE: LastError = 30001 ("SQL logic error or missing database")
[33]: SQLite.Query(db1, "CREATE INDEX objid ON obj (id);");
TRACE: LastError = 30001 ("SQL logic error or missing database")

Well it seems that it's the same thing if the index already exists...thanx TJ for the debug window hint!

BTW TJ, have you covered indexes in your tuts?

[TJ_Tigger]

BTW TJ, have you covered indexes in your tuts?

Not yet. Don't know if and or when I will. There is so much to cover but not enough time to put it all together. As it sits now, I will have two more shows released on the current topic/project and then switch gears to _CommandLineArgs. Based on the poll I put out there that seems to be a popular one. I need to finish show 10 and finialize the code for the _CommandLineArgs project so I can start recording. I think there will be at least 2 shows, maybe three depending on the other ideas I have. After that I don't know when I will come back to the SQLite plugin. It would be nice to cover triggers, views and indicies, we shall see.