2 questions : flat database and how to populate 1

[alix]

Hi Everyone,

I'm new at database and I need to create a rather large one. And then I want to use RB to extract useful information from it.
I have read through the basic doc but I can't find an answer for 2 simple questions.

1) In the RB Help file, it says : 'SQLite databases are kept in simple flat files' . And in the SQL online course, chapter 10, sqlcourse2.com/joins.html , it says 'Joins can be explained easier by demonstrating what would happen if you worked with one table only, and didn't have the ability to use "joins". This single table database is also sometimes referred to as a "flat table".'
So does this mean that Sqlite is a flat table kind of database ? where you can only have one table ? or where you can have several tables but you don't have the ability to use joins ?

2) What is the best way of filling up a database with data ? I thought of having several text files for containing the data. Each text file would correspond to a column of a table. And then, one could maybe somehow use RB to transfer the data from the text file into the database

Hope these questions are not too rudimentary ...

[kokenge]

SQLite files are held in simple flat files.
And it can contain lots of tables.
However they add information to it so that it knows what the schema looks like, and where stuff is located.

I guess I have to disagree with their explanation of what a flat table is and the ability to do joins. That information is wrong.

1. Most people call a DB table flat if it does not have any indexes.
2. You can do joins on a single table. It's is done all the time.

For Example: assume you have a list of persons, with orders showing the date and item ordered. If you want to find the last date and item they ordered you do a subSelect against itself.
The table order has personNum, orderNum, orderDate, and itemNum.
To find the this information you create a subselect as follows:

SELECT *
FROM     order
WHERE   orderDate = (SELECT MAX(ord1.orderNum)
              FROM order as ord1
              WHERE ord1.personNum = order.personNum)

Filling a DB.
Most sqlite database managers will let you load tables. However they are usually used to load small tables of a few thousand records or less. If you need to load millions of records, it's probably faster and easier to simply write a program.
Most DB managers also let you dump CSV files and Schema data.

Assume you dumped your DB into a CSV file using the standard where the fields are surrounded with double quote (") and separated with a comma (.), you can load your data doing something like this:

sqliteconnect    #sql,   "yourDb"        ' Connect to your DB

open DefaultDir$ + "\public\data.txt" for input as #f
while not(eof(#f))
line input #f, a$
a$	= dblQuote(a$)		' escape the single quote
a$	= strRep$(a$,"""","'") 	' replace double quote with single quote
print a$
    sql$ = insert into yourTableName values (";a$;")"
    #sql execute(sql$)
wend
close #f
#sql disconnect()
wait

' -----------------------------------------
' Convert single quotes to double quotes
' -----------------------------------------
FUNCTION dblQuote$(str$)
i    = 1
qq$  = ""
while (word$(str$,i,"'")) <> ""
	dblQuote$   = dblQuote$;qq$;word$(str$,i,"'")
	qq$ = "''"
	i   = i + 1
WEND
END FUNCTION

' --------------------------------
' string replace rep str with
' --------------------------------
FUNCTION strRep$(str$,rep$,with$)
ln	= len(rep$)
ln1	= ln - 1
i	= 1
while i <= len(str$)
    if mid$(str$,i,ln) = rep$ then
        strRep$ = strRep$ + with$
        i = i + ln1
    else
        strRep$ = strRep$ + mid$(str$,i,1)
    end if
i = i + 1
WEND
END FUNCTION

As far as writing a program to look up your information and maintain your database, there is already a program to do this.
Go to the Run Basic projects here
runbasic.wikispaces.com/Projects
Download 1. SQLite Database manager to maintain your DB,
and 2. RBgen - this will do your table query and maintenance you want.

Hope this helps
Have a great day.
Dan

[alix]

Thank you very much, kokenge, for your detailed answer. I'm still busy processing your information. It is very helpful.

[kokenge]

OOPS! Just happen to notice the SQL above to produce a list of the last order for every person is incorrect. It should look for the last date "MAX(orderDate) not the last number "MAX(orderNum).

It should look like this:

SELECT *
FROM     order
WHERE   orderDate = (SELECT MAX(ord1.orderDate)
              FROM order as ord1
              WHERE ord1.personNum = order.personNum)

Sorry!!