PlanetSquires Forums

Please login or register.

Login with username, password and session length
Advanced search  
Pages: 1 [2]

Author Topic: Example SQLite/SQlitening program for FF3  (Read 4928 times)

Paul Squires

  • Administrator
  • Guru Member
  • *****
  • Posts: 9015
  • Windows 10
    • PlanetSquires Software
Re: Example SQLite/SQlitening program for FF3
« Reply #15 on: November 10, 2009, 11:09:16 PM »

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.
Logged
Paul Squires
PlanetSquires Software
WinFBE Editor and Visual Designer

Rolf Brandt

  • Master Member
  • ****
  • Posts: 933
  • XP Pro SP3 - Vista - Win7
    • Taxi Dispatching Systems
Re: Example SQLite/SQlitening program for FF3
« Reply #16 on: November 11, 2009, 01:44:55 AM »

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

« Last Edit: November 11, 2009, 01:50:19 AM by Rolf Brandt »
Logged
Rolf Brandt
http://www.rbsoft.eu
http://www.taxifreeware.com
I cook with wine, sometimes I even add it to the food.
(W. C. Fields)

Marty Francom

  • Master Member
  • ****
  • Posts: 666
    • Pharmacy Software
Re: Example SQLite/SQlitening program for FF3
« Reply #17 on: November 11, 2009, 03:13:03 PM »

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"
Logged

Marty Francom

  • Master Member
  • ****
  • Posts: 666
    • Pharmacy Software
Re: Example SQLite/SQlitening program for FF3
« Reply #18 on: November 11, 2009, 03:58:10 PM »

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.
Logged
Pages: 1 [2]