SQLiteExpert Question

[Martin Francom]

I have been convinced that I should use SQLiteExpert to create the initial database for my projects.   Is there a specific Forum that would be best to ask questions that relate directly to the use of SQLiteExpert?   I looked at SQLiteExpert's web site and there doesn't seem to be any forums there.   I know many FF users also use SQLiteExpert so maybe this would be a good place to ask them.   There is also the SQLitening forum but there doesn't seem to be quite as much traffic there.  If there is a better place please inform me.

Well, I have a couple of questions to start:

   When creating fields you can "Declare Type"
        Is it always best to specify the field type?
        Is there times when you shouldn't?
        What is the difference between Number and Numeric?
        Is there some doc's that explain the different field types and when to use them?
        "Size" and  "Precision"  when should they be used?
       If you select "Not Null"  you can set what to do on a conflict.
          It seems that the most common responce would be abort.
          When (under what situations) would you want to use the other options?

  When creating Indexes there are several options available.   There are checkboxes
for "Primary" and "Unique"  why/when would you set them?
   There are Collate options  NOCASE, RTRIM, BINARY  when would you want to
set them?

   
     
   

[Rolf Brandt]

About Field Types in SQLite

Short answer:
Forget about them!


Long answer:

1. Quote from the SQLite documentation:
http://www.sqlite.org/different.html

Manifest typing
Most SQL database engines use static typing. A datatype is associated with each column in a table and only values of that particular datatype are allowed to be stored in that column. SQLite relaxes this restriction by using manifest typing. In manifest typing, the datatype is a property of the value itself, not of the column in which the value is stored. SQLite thus allows the user to store any value of any datatype into any column regardless of the declared type of that column. (There are some exceptions to this rule: An INTEGER PRIMARY KEY column may only store integers. And SQLite attempts to coerce values into the declared datatype of the column when it can.)
As far as we can tell, the SQL language specification allows the use of manifest typing. Nevertheless, most other SQL database engines are statically typed and so some people feel that the use of manifest typing is a bug in SQLite. But the authors of SQLite feel very strongly that this is a feature. The use of manifest typing in SQLite is a deliberate design decision which has proven in practice to make SQLite more reliable and easier to use, especially when used in combination with dynamically typed programming languages such as Tcl and Python.
[End of quotation]


2. Read also this part of the SQLite documentation about datatypes:
http://www.sqlite.org/datatype3.html
You will find this article also in the SQLitening helpfile under White Papers and in the Fred's ReadMe.


3. Ask Fred what he thinks of assigning datatypes to columns!
(He will basically tell you the above. Fred's words:)

One of the great features of SQLite is that you can create columns with no data type, which will result in an affinity of none, by using the following create syntax:
Create Table T1 (C1, C2, C3)
There is no advantage in assigning column affinity using the following code:
Create Table T1 (C1 Integer, C2 Text, C3 Real)


Basically it is sort of a religion if you want to assign field types or not. There are different opinions. SQLiteExpert simply gives you both options. You have to decide for yourself.

[Pat Dooley]

Marty,
SQLiteExpert is just a UI to SQLite. It's easy and handy and great to easily try out SQL statements. I'm glad to have it. However, it is just a UI.
Try this in SQLiteExpert:
Create a new database.
Modify the two create table statements from Rolf...

Code Select Expand


Create Table T1 (C1 Integer, C2 Text, C3 Real)
Create Table T2 (C1, C2, C3)

and execute them in the SQL window.
Now go to the Data tab and add some values.
Table T2  with untyped fields will allow anything. But table T1 restricts your input. But this restriction is all SQLiteExpert not SQLite itself.  Sort of like running all SQLitening input values through a FireTextBox to restrict what gets actually stored.
It doesn't appear that you can create untyped columns in SQLiteExpert using the Design tab, but you can using straight SQL.
Also, NOCASE basically means that searches will be case-insensitive. Pretty important consideration.
Pat
   

[Brian Chirgwin]

  When creating Indexes there are several options available.   There are checkboxes
for "Primary" and "Unique"  why/when would you set them?
   There are Collate options  NOCASE, RTRIM, BINARY  when would you want to
set them?       

A primary key is the field (or fields) that allow you to select an individual row of the database. Customer Number of a Customer Table, Invoice Number of an Invoice Table, would be primary fields of a table. Using Customer ID =999 will always select the customer with 999. A primary key MUST have a value; Nulls not allowed.

Unique means the value in can only appear in one row in the database except for null. Null can appear in many. For example, a table with a field such as Social Security Number. This field is unique; only one customer can have a given SSN. SSN makes a bad PRIMARY KEY because not everyone may not have an SSN. For example, international customers that don't have an SSN?

Using primary/unique will provide errors when a row is added that already exists in the database. Inserting a records with the same value as an existing record will result in an error. For example,

Insert Into Customer (CustomerID) VALUES 1;
Insert Into Customer (CustomerID) VALUES 1;  <--- This insert will fail with an error result because CustomerId 1 already exists in the table.

A primary key must be unique and not allow nulls.
A unique key must be unique accept for the value NULL.

By the way this information is fundamental Relational Databases stuff, not just SQLite specific. Access, SQL Server, Oracle, MySQL, etc... all use primary and unique indexes.


Hope this helps.

[Martin Francom]

Thanks Guys.... This has been very helpful.  What I am hearing is:

Don't assign a Type to a column.  No advantage to do so.
Using a column as a unique record ID  I would assign it the property Primary/Unique.
If I am using a column as an index field and the data is alphebetic based then using "Collate Nocase"  will keep the column index alphabetically reguardless if how the user has entered the data.

When an error occurs, how does SQLite handle the error?  While trying stuff I have occassioned apon a Pop Up message box that explane an error (I assume this is coming from SQLite) and when I close the message box my pogram promtly ends.  What's the best way to handle errors?

If I created a database with SQLiteExpert, Is there a call to SQLite/SQLitenting that will return a string of the Column names for that particular TABLE ? Or, does the column names have to be hard coded in the program?
   My understanding is that when doing an Update to a record you need to supply "slBuildIsnsertOrUpdate" call with the field values and the Column names.   

[Martin Francom]

Another question:
   SQLiteExpert has a checkbox in the area where you can create/modify Indexes.  This checkbox is "AutoIncrement".   If checked, does that mean SQLite will automatically assign the next higher value to that field when the next record is added?
   Lets say I have a field "RecNum" which I want to be unique and to automatically increment as records are added.  What properties should I assign that Column?
   My guess would be:
             Type = numeric 
             Check "Not Null"  checkbox
             On Conflick =  ABORT
             Check Index Property "AutoIncrement"

That means if I understand correctly that when a record is added this filed will be automatically
added and increment.  So, in the field string that I supply to the add record call I would filed as a null string and SQLite will add and increment it.  Correct?

Hummmm.  I been playing with the settings.   I can't get the autoincrement to work as I thought it would.  Can someone explane the Autoincrement and how to properly set it. ?