• Welcome, Guest. Please login.
 
September 24, 2020, 09:41:19 am

News:

Welcome to the SQLitening support forums!


Fred, some clarification please

Started by Marty Francom, November 19, 2009, 05:05:57 am

Previous topic - Next topic

Marty Francom

November 19, 2009, 05:05:57 am Last Edit: November 19, 2009, 05:18:36 am by Marty Francom
Fred,
   Going back to what you said:  If I understand correctly;  I should create the data base this way:


   t1 = "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, Name, Add1, Add2, " & _
        "City, State, Zip, Email, Phone, ax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
        "Unique(Company, NCPDP, State, AExp, Lexp, OExp));"   

   slExe "Begin"  'This starts the transaction
      slExe t1, "E0"
      slExe "CREATE INDEX NCPDP ON Customers (NCPDP);", "E0"
      slExe "CREATE INDEX Company ON Customers  (Company);", "E0"
      slExe "CREATE INDEX AExp ON Customers (AExp);", "E0"
      slExe "CREATE INDEX LExp ON Customers (LExp);", "E0"
      slExe "CREATE INDEX OExp ON Customers (OExp);", "E0"
      slExe "CREATE INDEX State ON Customers (State);", "E0"
   slExe "End"



My biggest problem is that I don't exactly know the syntax for the SQLite language.  I have no clue what the "EO" or  "CEO"  does.   Is there a good simple, short primer documentation that would explain the syntax?   Sort of a "getting started" with SQLite programming. 

I noticed you have a Tutorial Topic section.    It would be great if someone  could write up a step by step instructions for building a simple SQLite database.  And, explane the syntax that is being used and what is does.

Marty Francom

I have been using version 1.20  and the  pdf file that came with it was 32 pages long.  Only problem all the pages were blank,

I just downloaded the 1.30 version and to my delight the pdf doc's are there and are readable.  I will read through this and see if I can begin to get my head around it.   Thanks!   

Marty Francom

The  SQLitening.PDF and SQLItening.TXT file are both readable.   But the SQLitening.CHM file is not functioning.   The topics list shows, but the topic content does not show.

Fred Meier

November 19, 2009, 12:05:01 pm #3 Last Edit: December 01, 2009, 03:41:59 pm by Fred Meier
Marty, your creates are correct but I would code it as follows:

   slExe "Begin"  'This starts the transaction
   slExe "Create Table Customers (Company collate NoCase,  NCPDP, UserCount, Name, Add1, Add2, " & _
         "City, State, Zip, Email, Phone, ax, Cell, NPI, Optional, AExp, LExp, OExp, ALic, LLic, OLic, Note, " & _
         "Unique(Company, NCPDP, State, AExp, Lexp, OExp))"   
   slExe "Create Index NCPDP ON Customers (NCPDP)"
   slExe "Create Index Company ON Customers  (Company)"
   slExe "Create Index AExp ON Customers (AExp)"
   slExe "Create Index LExp ON Customers (LExp)"
   slExe "Create Index OExp ON Customers (OExp)"
   slExe "Create Index State ON Customers (State)"
   slExe "End"


The only difference is I removed the optional ModChars and the optional
semicolon.  Nothing actually changed but it is just cleaner code IMO. 

I will put together a topic about error handling and post in the Tutorials and Training section.
See http://www.sqlitening.com/support/index.php?topic=3150.0

My SQLitening.Chm works fine here, maybe Rolf (he is the author) can help you with yours.

Marty Francom

Fred,
     Now that I have some doc's I want to take the AdBook and go through and comment it highly, explaining what  each call does.  This will be a good learning exersize  for me.  I want to also add a "record generator"   that can add thousand of records.  I would like to see how well this method of accessing the database works when the database has a thoudand, ten thousand, million records.  Obviously to add that many records in a short time will require a record generator.  I don't want a million records with the same data so figuring out a good way to created a lot of random records is necessary.  Any suggestions?

   When I get that all done, I will repost the project and then maybe we can disect it and discuss it.  Might be helpful for others trying to learn SQLItening.
 

Gary Stout

Marty,

Don't know if it would help, but Tsunami used to have a demo that would generate names and addresses. I think it max'd out at 30000 records. Not sure if that is still around on PB's board or not. I may still be able to dig it up.

Gary
Gary Stout
gary@sce4u.com

Marty Francom

Gary,
   Yes, that would be a good start.  If you find it please post a link or attach the file.  Thanks

Gary Stout

Marty,

I have not been able to find the Tsunami demo. If you are a registered user, you might be able to email Timm Motl and have him send it.

Sorry,
Gary
Gary Stout
gary@sce4u.com

David L Morris

Hello Marty

If you can get the DATA0030.TXT file from Tim as suggested by Gary, it contains 30000 records. I have played with PBCC40 to convert the file to csv and load it into an SQLite database.  I am sure you will not need all the indexes, just the INTEGER PRIMARY KEY will suffice.  The data loads in less then a minute. A "select * from clients where state = 'WI' " returns 2748 records in 29.13ms , so indexes on all fields is a waste to me.

Regards
David

Gary Stout

Quote from: David L Morris on November 26, 2009, 07:51:12 am
Hello Marty

If you can get the DATA0030.TXT file from Tim as suggested by Gary, it contains 30000 records.


Marty,

Let me take a look when I get back home from the holiday. I think I may have the data text file, but I am not sure it has 30000 records. I do remember that the demo would default to 5000 records and could be set as high as 30000. I think I still have some form of the data text, but I don't remember how many records it contains.
It will be saturday before I am back home and able to access those.
Gary Stout
gary@sce4u.com