Hello everyone,
This is espcially for Dr. Hipp.
First I will try to highlite the reasons as to why I am raising this
topic just to establish proper grounds for what I am going to Say and
Suggest.
I am a software developer using VB as my main development tool and an
trying to learn Delphi but the process is very slow for me as
learning Delphi does'nt come in my priority. I don't know C or C++
(in fact I left C/C++ behind when I saw VB version 2.0) and am not
well versed with SQL scripting either.
I also work as a freelance consultant for a few firms here in my
region (Rajkot, Gujarat, INDIA). My areas of consultancy are
computerising business operatins, helping my clients to convert their
manual process to computer, help clients in purchasing the best
computer harware/software appropriate to their business requirement,
managing software development of clients and helping them develop
their own software inhouse by their own inhouse comuter talents,
suggest them which back end database to use for their requirements,
etc.. etc...
I have been following SQLite's progress for quite some time now. I
have got a few IDEAS regarding enhancements to SQLite to make it even
more powerfull and faster than what it is today.
Before I go any further. Let me confess that I have never used SQLite
in any commercial projects because I feel it requires more work to
use SQLite in VB. And for people like me working in VB the question
of SIZE is immaterial, what we people prefer the mose it ease of use.
The features that I am suggesting here will not affect the current
functionality of SQLite in any but it will just enhance it in multi
folds. So here are my IDEAS:
-----------------------------------------------------------------------------------------------
Storing Collections Of Values In A Single Column
-------------------------------------------------
Traditional relational databases restrict one to storing one value in
each column. Let us add such a facility to SQLite to store
collections of values in a single column. With collections, one would
no longer need to create multiple columns (or separate tables) for
multiple street addresses, phone numbers, status codes, and so on.
Instead, one just define a column as a collection, and can store as
many or as few values in that column as one wants. There should be no
limit on the number of values a collection column can hold.
I think Collection columns are well suited for situations where one
has an unknown number of data items of the same type. Consider the
case of the Phone column. Instead of allowing only one telephone
number or perhaps a small, fixed number (Phone1, Phone2, and so on),
one can define the Phone column as a collection. If some rows have a
dozen phone numbers while most others have only one or two, a
collection works well (I feel so). When multiple values are added to
the collection, SQLite would (probably) delimits multiple values with
a separator character.
I think that in many cases, one can use collections to replace
foreign tables and, thereby, eliminate joins that would have to be
executed to retrive data. This can dramatically improve query
performance.
To query collections, SQLite can inmplement an ANY clause in the
SELECT statement as an extension to ANSI SQL to conveniently access
the collection values either individually or as a group.
Storing User-Defined Structures In A Single Column
--------------------------------------------------
SQLite can provide facility to allow the user to create and define
structures, or groups of columns separately (kind of separate logical
table), and store those structures in a single column in the main
table. This grouping can make it easier to define tables that happen
to contain the same kind of information. Structures should be allowed
to include other structures, but should not be allowed to be defined
recursively. As per previous suggestion just like normal columns,
structures can be collections. In effect, this lets one nest an
entire table in a single column. Moreover, because one can nest
collections and structures to any depth, one can store even highly
complex objects in a single column.
At the SQL from just add an extensions that lets one access the
entire structure as a single entity, or directly access specific
components within the structure.
For example, one might define an Address structure that contains
Street, City, State, and ZipCode columns. Let us assume that this
Address structure appears both in the Customers table and in the
Employees table. Say when one's company goes multi-national and one
need to redefine the Address structure to allow for international
address formats generally one whould have to make changes in all such
Tables which contain such informations so loking at the example one
would have to make changes in teh Customer Table as well as in
emplouees Table. Imagine how easy it can get as one will only have to
make change in the schema in one place: the definition of the Address
structure and each table that references the Address structure will
change automatically.
I will just eleborate with another simple example:
Let us assume a sample contact database. In this Database to store a
type for each phone number office, direct dial, fax, home, cellular,
and so on. Accodring to the RDBMS Model, one will have to create a
separate Phones table. Each contact would have a row in the Contact
table, as well as multiple rows in the Phones table.
If such a feature is implemented in SQLite then one would just create
a structure, PhoneStructure, that
contains two columns: PhoneNumber and PhoneType. Then one would add a
Phones column to the Contact table and, for the data type of that
column, specify PhoneStructure. Because the Phones column contains
PhoneStructure, one can store values both for phone number and for
phone type in that one column in the Contact table. In addition, if
the Phones column is defined as a collection, one can store multiple
phone numbers/phone types in that column.
Looking at this example one can understand as to how much data a
standard relational database must assemble when it joins a Contact
table with a Phones table, but if such a facility is made available
in SQLite imagine how clean and compact a complex row would be.
I KNOW THAT SQLITE IS "TYPELESS" but to implement the above features
probably it would have to change from that..?!
RefTo Columns: Using Row IDs To Replace Keys And Eliminate Joins
----------------------------------------------------------------
Every SQLite table contains a Row ID column by default (if I am not
wrong). Each time one adds a row to a table, SQLite automatically
enters a value in the Row ID column that is unique throughout the
entire current table. Using this feature of SQLite a feature which I
call RefTo can be added which will allow a field to refer to a
complete separate Table in the Database.
For Example : Let us return back to the sample Contact database. In
this database one would create a separate Phones table in SQLite just
as one would do in a traditional relational database. However,
instead of adding a PhoneId column to both the Contacts Table and the
Phones Table (as it would be necessary for convensional relational
database), one can simply add a PhoneRef column to the Contacts
table. This PhoneRef column contains the Row IDs of the appropriate
rows in the Phones table. If PhoneRef is defined as a collection, it
can contain the Row IDs of multiple rows from the Phones table.
I think that Using the PhoneRef column in the Contacts table, one can
access values in the Phones table without any expensive joins.
I personally feel that if the above facilities are implemented in
SQLite then SQLite would not just be a Relational Database System but
would become an Object-Relational Database System. Which is very easy
to implement and use (as a collection of structures is often more
efficient and easier to write than the corresponding relational
n-table join query).
Now Something About How To Implement SQL Query Features In SQLite
-----------------------------------------------------------------
Accessing values in a collection
--------------------------------
To access values in a collection, privide an SQL ANY clause, which is
a nothing but an extension to ANSI SQL as already implemented in
SQLite. If the user specify ANY after the column name, SQLite will
operate on all of the values in a collection.
For example, if the Phones column is a collection, one can find all
rows that contain the phone number 206-481-4442 by specifying the
following SELECT statement:
SELECT LastName
FROM Contacts
WHERE Phones(ANY) = "2064814442"
If one always store fax numbers in the third element of the Phones
collection, one could find all rows that contain a particular fax
number
like this:
SELECT LastName
FROM Contacts
WHERE Phones(3) = "2064814442"
Accessing Data In Structures And In Other (ReferTo) Tables
----------------------------------------------------------
To access values in a structure or in another table, SQLite will have
to provide an operator. My suggestion is to use (!) exclamation mark
as separator. To access a column in a user-defined structure, one
simply specify the structure name, an exclamation ( ! ), and the
column name.
For example, let’s return to the contact database, which contains a
Phones column whose
data type is PhoneStructure, so the Phones column contains the
columns PhoneType and PhoneNum. The following query could returns
just the phone number 206-402-9400 and not the phone type for that
number:
SELECT Phones(*)!PhoneNum
FROM Contacts
WHERE LastName = "Calder"
The following query would return the phone numbers for every contact
who has a fax number:
SELECT Phones(*)!PhoneNum
FROM Contacts
WHERE Phones(ANY).PhoneType = "fax"
Suppose that we put the PhoneType and PhoneNum in a separate table
instead of in a PhoneStructure. To tie rows in the Phones table to a
contact in the Contacts table, we then add a RefTo collection column
called PhoneRef to the Contacts table. This RefTo column contains the
Row IDs of one or more rows in the Phones table. To access phone
numbers in the Phones table, one uses an identical query (except for
minor name changes for clarity):
SELECT PhoneRef(*)!PhoneNum
FROM Contacts
WHERE PhoneRef(ANY).PhoneType = "fax"
This means that, if one would link tables using Row IDs, there would
be no need to write a join clause and specify what key columns to use
every time one want to access data in another table!
I think SQLite can returns just the data you need Like This
Recall that traditional relational queries require a join to produce
a invoice which includes line items. As a result, when one query for
a single invoice, one get rows back that look like this. Note that
all of the data from the Master table is redundant after the first
row:
>From Master table From foreign table
----------------- -------------------
InvNo Date CustId PartNo Qty Price Ext_price
1768 11/06/59 MCDNLD A1617 3 45.87 137.61
1768 11/06/59 MCDNLD C4587 1 51.00 51.00
1768 11/06/59 MCDNLD A1681 3 6.00 18.00
1768 11/06/59 MCDNLD A1680 7 45.87 137.61
1768 11/06/59 MCDNLD D3298 5 12.50 62.50
1768 11/06/59 MCDNLD D3299 5 12.50 62.50
SQLite will then lets one get just the data one needs. The rows
returned by SQLite are structured just like conventional data. One
don’t have to search though the results to find all of the identical
InvNo columns. SQLite returns exactly one invoice (include line
items) per row. Again,
structured just like conventional data.
SQLite shoudl also provide Direct, independent access to data in
collections and structures
-------------------------------------------------------------------------------------------
One can query data in a collection or structure-valued column just as
one can query data in a conventional table. To the query interface, a
collection of structures should appears as a logical table with its
parent table name automatically prefixed. So, for instance, a query
to find all LineItems in the Invoices table would look like this:
SELECT PartNo, QtyOrdered, UnitPrice, QtyOnHand, EditDate
FROM Invoices.LineItems
So one can see that one would not lose anything by using SQLite's
extended storage model (if implemented). Even though the LineItems
information is embedded into the Invoices table, one can still write
a simple query to access it as if it were in an independent table.
-----------------------------------------------------------------------------------------------
WOW that was a long message...!!!.!.!.! (:)
>From what little of SQLite I have used I can see that it can be a
robost RDBMS which will give commercial RDBMS suppliers a run for
their money. It is with this IDEA that I made above suggestions.
If the this Forum/Group is interested I have other suggestins
regarding SQLite also.
Thanks for reading till the END.
From
Yogi Yang
email : [EMAIL PROTECTED]
Laser Academy Of Computer
321, Heera Panna, Dr. Yagnik Road,
Rajkot - 360 001. Gujarat, INDIA
__________________________________
Do you Yahoo!?
New Yahoo! Photos - easier uploading and sharing.
http://photos.yahoo.com/
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
