Example SQLite/SQlitening program for FF3

[Paul Squires]

When you create your indexes, you can do so without regard to casing by using the COLLATE NOCASE statement.

CREATE INDEX [Company] ON [Customers] ([Company] COLLATE NOCASE);

Also, when you create your index, shouldn't the fields making up the multiple index key be separated?

You are doing this:
   slExe "CREATE INDEX Company ON Customers UPPER ((Company & rowid));", "E0"

When maybe you could do this?
   slExe "CREATE INDEX Company ON Customers (Company COLLATE NOCASE, rowid);", "E0"

Also, when creating all of the tables and indexes, you can combine multiple slExe calls into one:
   'We are doing a transaction, sending several SQL statements to the server
   'Build tables and indexes
   t1 = "BEGIN IMMEDIATE;" & _
        "      CREATE TABLE Customers ("   & Flds & ");"     & _
        "      CREATE INDEX NCPDP ON Customers (NCPDP);"     & _
        "      CREATE INDEX Company ON Customers (Company, RowID);" & _
        "      CREATE INDEX [A-Exp] ON Customers ([A-Exp]);" & _
        "      CREATE INDEX [L-Exp] ON Customers ([L-Exp]);" & _
        "      CREATE INDEX [O-Exp] ON Customers ([O-Exp]);" & _
        "      CREATE INDEX State ON Customers (State, RowID);"     & _
        "END;"
   slExe t1, "E0"

I see what you are trying to do with the current record and displaying the 3 most previous and 3 records afterwards. Unless I was dealing with a million records then I would simply do a SELECT with an ORDER BY and bring the whole data set into a local array and dealing with navigating that array of data rather than constantly trying to SELECT the 3 previous and 3 next records. It just seems like it would be a hell of a lot simpler design and easier to maintain. That's just my opinion of course. You may have a much more valid reason for doing what you are doing.

[Rolf Brandt]

Hi Marty,

first about creating Indexes of multiple fields. The syntax would be:

slExe "CREATE INDEX IndexName ON TableName FieldName1, FieldName2;", "E0"

The fields used for the index must be separated by commas.
The index on the field "Company" should probably be unique.
The indexes on fields like "A-Exp" cannot be unique because they contain dates, and you will have these values more than once in the field. The same applies to the field "State".

Field "uniquenumber"? Basically this is there already. SQLite creates that for you. It is the field "rowid". It is a unique number that gets automatically incremented by SQLite. You do not have to waste a line of code on that. In our example project the rowid is shown in the label "Rec#" (lblRecNo).

Here you find a description of rowid and autoincrement fields:
http://www.sqlite.org/autoinc.html

Rolf

[Martin Francom]

Hi Marty,

first about creating Indexes of multiple fields. The syntax would be:

slExe "CREATE INDEX IndexName ON TableName FieldName1, FieldName2;", "E0"

The fields used for the index must be separated by commas.
The index on the field "Company" should probably be unique.
The indexes on fields like "A-Exp" cannot be unique because they contain dates, and you will have these values more than once in the field. The same applies to the field "State".

Field "uniquenumber"? Basically this is there already. SQLite creates that for you. It is the field "rowid". It is a unique number that gets automatically incremented by SQLite. You do not have to waste a line of code on that. In our example project the rowid is shown in the label "Rec#" (lblRecNo).

Here you find a description of rowid and autoincrement fields:
http://www.sqlite.org/autoinc.html

Rolf

Paul, Rolf,  Thank you.  Using a unique number to the index does solve the browse problems I was having.   The program now browses correctly.  Though I am problably doing more work than necessary to make sure the key index fields are unique.

Rolf,  Is there a way to know what the next unique ROWID number is going to be BEFORE the record is saved?   If so, how for I find that number.

That may be not be necessary if I can create an index using the key Field and the ROWID number.   Would this be the correct syntax for the SQL command?

slExe "CREATE INDEX IndexName ON TableName FieldName1, FieldName2;", "E0"

slExe "CREATE INDEX Company ON Customers  (Company, rowid);", "E0"

[Martin Francom]

Well, I tryed using  combining the field and rowid in the create index and that did not solve the browse problem so  I went back to my unique number method and the program functions correctly.

Next step to to add 10,000 records and see how it performs.  Just need to figure out a good, easy programmatically way to do that.  Any sugestions would be appreciated.

I am going to start another post and attach the corrected version the sample AdBook.  This one functions correctly browsing any index with out errors.