Idea for a new SQL Data Type:

 RECURRINGCHAR

The idea with RECURRINGCHAR is treated exactly like a VARCHAR in it's
usage.  However, it's designed for table columns that store a small set of
repeated values (<=256 values). This allows for a great deal of savings in
the storage of the data.

Example:

  Query:
    select count(*) from order
  Returns:
    100,000

  Query:
    select distinct status from order
  Returns:
    OPEN
    REWORK
    PLANNED
    RELEASED
    FINISHED
    SHIPPED

It's apparent that there is a lot of duplicate space used in the storage
of this information.  The idea is if order.status was stored as a
RECURRINGCHAR
then the only data stored for the row would be a reference to the value of
the column. The actual values would be stored in a separate lookup table.

Advantages:

 - Storage space is optimized.

 - a query like:

    select distinct {RECURRINGCHAR} from {table} 

   can be radically optimized

 - Eliminates use of joins and extended knowledge of data relationships
          for adhoc users.

This datatype could be extended to allow for larger sets of repeated
values:

 RECURRINGCHAR1 (8-bit)   up to 256 unique column values
 RECURRINGCHAR2 (16-bit)  up to 65536 unique column values

Reasoning behind using 'long reference values':

It is often an advantage to actually store an entire word representing a
business meaning as the value of a column (as opposed to a reference
number or mnemonic abbreviation ).  This helps to make the system 
'self documenting' and adds value to users who are performing adhoc
queries on the database.

----
David Bennett
President - Bensoft
912 Baltimore, Suite 200
Kansas City, MO  64105




Fri, 29 Jun 2001 17:05:35 -0500 (CDT)
<dbennett@jade.bensoft.com>
New SQL Datatype RECURRINGCHAR
This is rather like MySQL's enum.  I still opt for the join, and if
you like make a view for those who don't want to know the data
structure.
--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: <dbennett@jade.bensoft.com>
To: <pgsql-hackers@postgresql.org>
Sent: Friday, June 29, 2001 6:05 PM
Subject: [HACKERS] New SQL Datatype RECURRINGCHAR


> Idea for a new SQL Data Type:
>
>  RECURRINGCHAR
>
> The idea with RECURRINGCHAR is treated exactly like a VARCHAR in
it's
> usage.  However, it's designed for table columns that store a small
set of
> repeated values (<=256 values). This allows for a great deal of
savings in
> the storage of the data.
>
> Example:
>
>   Query:
>     select count(*) from order
>   Returns:
>     100,000
>
>   Query:
>     select distinct status from order
>   Returns:
>     OPEN
>     REWORK
>     PLANNED
>     RELEASED
>     FINISHED
>     SHIPPED
>
> It's apparent that there is a lot of duplicate space used in the
storage
> of this information.  The idea is if order.status was stored as a
> RECURRINGCHAR
> then the only data stored for the row would be a reference to the
value of
> the column. The actual values would be stored in a separate lookup
table.
>
> Advantages:
>
>  - Storage space is optimized.
>
>  - a query like:
>
>     select distinct {RECURRINGCHAR} from {table}
>
>    can be radically optimized
>
>  - Eliminates use of joins and extended knowledge of data
relationships
>           for adhoc users.
>
> This datatype could be extended to allow for larger sets of repeated
> values:
>
>  RECURRINGCHAR1 (8-bit)   up to 256 unique column values
>  RECURRINGCHAR2 (16-bit)  up to 65536 unique column values
>
> Reasoning behind using 'long reference values':
>
> It is often an advantage to actually store an entire word
representing a
> business meaning as the value of a column (as opposed to a reference
> number or mnemonic abbreviation ).  This helps to make the system
> 'self documenting' and adds value to users who are performing adhoc
> queries on the database.
>
> ----
> David Bennett
> President - Bensoft
> 912 Baltimore, Suite 200
> Kansas City, MO  64105
>
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>


Tue, 3 Jul 2001 16:07:01 -0400
"Rod Taylor" <rbt@barchord.com>
Re: New SQL Datatype RECURRINGCHAR
This is not a good idea. You are probably coming from mysql background (no
offense :).

See comments inline.

On Fri, 29 Jun 2001 dbennett@jade.bensoft.com wrote:

> Idea for a new SQL Data Type:
> 
> It's apparent that there is a lot of duplicate space used in the storage
> of this information.  The idea is if order.status was stored as a
> RECURRINGCHAR
> then the only data stored for the row would be a reference to the value of
> the column. The actual values would be stored in a separate lookup table.
You should instead have another table with two columns, order_status_id
and order_status_desc, and join with it to get your data.
> 
> Advantages:
> 
>  - Storage space is optimized.
> 
>  - a query like:
> 
>     select distinct {RECURRINGCHAR} from {table} 
> 
>    can be radically optimized
select distinct order_status_desc from order_status_lookup

>  - Eliminates use of joins and extended knowledge of data relationships
>           for adhoc users.
For adhoc users, you can create a view so they won't be aware of joins. 

> It is often an advantage to actually store an entire word representing a
> business meaning as the value of a column (as opposed to a reference
> number or mnemonic abbreviation ).  This helps to make the system 
> 'self documenting' and adds value to users who are performing adhoc
> queries on the database.
No, that is against good database design and any database normalization.

-alex


Tue, 3 Jul 2001 16:32:21 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: New SQL Datatype RECURRINGCHAR
>> It's apparent that there is a lot of duplicate space used in the storage
>> of this information.  The idea is if order.status was stored as a
>> RECURRINGCHAR
>> then the only data stored for the row would be a reference to the value
of
>> the column. The actual values would be stored in a separate lookup table.

>You should instead have another table with two columns, order_status_id
>and order_status_desc, and join with it to get your data.

The idea is to simplify the process of storing and accessing the data.
Joins required
a deeper knowledge of the relational structure.  This also complicates
application
programming,  two tables must be maintained instead of just one.

>>     select distinct {RECURRINGCHAR} from {table}
>>
>>    can be radically optimized

> select distinct order_status_desc from order_status_lookup

Again the idea is to simplify.  Reduce the number of tables required to
represent a business model.

>>  - Eliminates use of joins and extended knowledge of data relationships
>>           for adhoc users.

> For adhoc users, you can create a view so they won't be aware of joins.

Now we have a master table,  a lookup table AND a view?
even more complication....

>> It is often an advantage to actually store an entire word representing a
>> business meaning as the value of a column (as opposed to a reference
>> number or mnemonic abbreviation ).  This helps to make the system
>> 'self documenting' and adds value to users who are performing adhoc
>> queries on the database.

> No, that is against good database design and any database normalization.

I would like to hear your argument on this.  I don't see how optimizing
the storage of reference value breaks a normalization rule.

--Dave


Tue, 3 Jul 2001 16:23:40 -0500
"David Bennett" <dbennett@bensoft.com>
RE: New SQL Datatype RECURRINGCHAR
"Rod Taylor" <rbt@barchord.com> writes:
> This is rather like MySQL's enum.

Yes.  If we were going to do anything like this, I'd vote for stealing
the "enum" API, lock stock and barrel --- might as well be compatible.

			regards, tom lane

Tue, 03 Jul 2001 17:49:18 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New SQL Datatype RECURRINGCHAR 
On Tue, 3 Jul 2001, David Bennett wrote:

> The idea is to simplify the process of storing and accessing the data.
> Joins required a deeper knowledge of the relational structure.  This
> also complicates application programming, two tables must be
> maintained instead of just one.
Sometimes, to maintain correctness, its necessary to have complex designs. 

"All problems have simple, easy-to-understand, incorrect solutions".

> Again the idea is to simplify.  Reduce the number of tables required to
> represent a business model.
Why? You should normalize your data, which _increases_ number of tables.


> >>  - Eliminates use of joins and extended knowledge of data relationships
> >>           for adhoc users.
> 
> > For adhoc users, you can create a view so they won't be aware of joins.
> 
> Now we have a master table,  a lookup table AND a view?
> even more complication....
Well, that's called software development. If you don't want complications,
you can use MS-Access *:)

> 
> >> It is often an advantage to actually store an entire word representing a
> >> business meaning as the value of a column (as opposed to a reference
> >> number or mnemonic abbreviation ).  This helps to make the system
> >> 'self documenting' and adds value to users who are performing adhoc
> >> queries on the database.
> 
> > No, that is against good database design and any database normalization.
> 
> I would like to hear your argument on this.  I don't see how optimizing
> the storage of reference value breaks a normalization rule.
What if tomorrow you will need to change text name for "OPEN" status to
"OPEN_PENDING_SOMETHING"? With your design, you will need to update all
rows in the table changing it. With normalized design, you just update the
lookup table. Etc, etc.

-alex




Tue, 3 Jul 2001 18:16:08 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
RE: New SQL Datatype RECURRINGCHAR
The only problem with 'enum' is that all of the possible values must be
specified at CREATE time.  A logical extension to this would be to allow for
'dynamic extensions' to the list.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Tuesday, July 03, 2001 4:49 PM
To: Rod Taylor
Cc: dbennett@jade.bensoft.com; pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] New SQL Datatype RECURRINGCHAR


"Rod Taylor" <rbt@barchord.com> writes:
> This is rather like MySQL's enum.

Yes.  If we were going to do anything like this, I'd vote for stealing
the "enum" API, lock stock and barrel --- might as well be compatible.

			regards, tom lane


Fri, 6 Jul 2001 17:44:21 -0500
"David Bennett" <dbennett@bensoft.com>
RE: New SQL Datatype RECURRINGCHAR 
> various disagreements and "quotes"...

I agree that you disagree.... :)

RECURRINGCHAR does not break normal form.  It simply optimizes the storage
of reference values (recurring keys).  This allows for the use of  'long
words' as reference values with a great deal of system storage savings and a
boost in performance in certain circumstances.  This is more a form of
'compression' then anything else, as a matter of fact,  this is very similar
to the LZ78 family of substitutional compressors.

  http://www.faqs.org/faqs/compression-faq/part2/section-1.html

The advantage here is that we are targeting a normalized value in it's
atomic state,  The recurrence rate of this these values is extremely high
which allows us to store this data in a very small space and optimize the
access to this data by using the 'dictionary' that we create.

>What if tomorrow you will need to change text name for "OPEN" status to
>"OPEN_PENDING_SOMETHING"? With your design, you will need to update all
>rows in the table changing it. With normalized design, you just update the
>lookup table. Etc, etc.

In either model you would:

	update master_table set status='OPEN_PENDING_SOMETHING' where status='OPEN'

This would not change,  in fact,  even in a normalized design you wouldn't
change the lookup table (parent) key.  Perhaps you are misunderstanding my
initial concept.  The MySQL 'enum' is close.  However,  it is static and
requires you to embed business data (your key list) in the DDL.  The idea I
have here is to dynamically extend this list as needed.  I am not saying
that the value can't relate to a parent (lookup) table.  It's just not
necessary if the value is all that is needed.

--Dave (Hoping some other SQL developers are monitoring this thread :)


Fri, 6 Jul 2001 18:24:48 -0500
"David Bennett" <dbennett@bensoft.com>
RE: New SQL Datatype RECURRINGCHAR
On Fri, 6 Jul 2001, David Bennett wrote:

<rest snipped>
> In either model you would:
> 
> 	update master_table set status='OPEN_PENDING_SOMETHING' where status='OPEN'
> 
> This would not change, in fact, even in a normalized design you
> wouldn't change the lookup table (parent) key.  Perhaps you are
> misunderstanding my initial concept.  The MySQL 'enum' is close.  
> However, it is static and requires you to embed business data (your
> key list) in the DDL.  The idea I have here is to dynamically extend
> this list as needed.  I am not saying that the value can't relate to a
> parent (lookup) table.  It's just not necessary if the value is all
> that is needed.
You are making absolutely no sense. 

Let me break it down:


a) To do an update of a key to a different value, you would need to do
following:
1) look up the new value in entire table, find if its already exists
2) If it exists, good.
3) if it doesn't, pick a next number. (out of some sequence, I suppose) to
represent the key.
4) do the actual update.

Step 1 without an index is a killer. Then, you need to have a certain
'table' to map the existing key values to their numerical representations.

How would this 'table' get populated? On startup? On select?

Its one thing to take 'enum' datatype, which I wouldn't disagree too
much with. Its another thing to suggest this kind of a scheme, which
should be really done with views and rules.

I.E. instead of (as you would have) table a(...,  x recurringchar), 
you must have two things:

table a_real (..., x int4)
table lookup (x int4, varchar value)

Then, have a view:
create view a as select ..., value from a_real, lookup where
a_real.x=lookup.x

Then create a rule on insert: (syntax may be rusty)
create rule foo
on insert on table a
do instead
...whatever magic you need to do the actual inserton, lookup, etc.


> --Dave (Hoping some other SQL developers are monitoring this thread :)



Fri, 6 Jul 2001 21:25:09 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
RE: New SQL Datatype RECURRINGCHAR
Alex,

I think I fully understand your position. Let me put wrap up our
conversation so far.


Given the application requirements:

  1) contacts have a type.

  2) new types must be added on the fly as needed.

  3) types names rarely change.

  4) the number of contacts should scale to support millions of records.

  5) the number of types will be limited to under 64k

  6) Users must be able to easily query contacts with readable types.


-----
In a nutshell you are recommending:
-----

  create table contact_type (
    code	  int2,
    type    char(16),
    PRIMARY KEY ( code )
  );

  create table contact (
    number  	serial,
    name    	char(32),
    type          int2,
    PRIMARY KEY ( number ),
    FOREIGN KEY ( type ) REFERENCES contact_type ( code )
  );

  create view contact_with_readble_type as (
    select c.number as number,
           c.name as name,
           t.type as type
    from
           contact c,
           contact_type t
  );

* To build a type lookup table:

  1) Select type and code from contact_type
  2) Build UI object which displays type and returns code

* In order to insert a new record with this model:

  1) Look up to see if type exists
  2) Insert new type
  3) Get type ID
  4) Insert contact record

* The adhoc query user is now faced with
  the task of understanding 3 data tables.

-----
With recurringchar you could do this easily as:
-----

  create table contact (
    number  	serial,
    name    	char(32),
    type          recurringchar1,
    PRIMARY KEY ( number ),
  );

* To build a type lookup table:

  1) Select distinct type from contact (optimized access to recurringchar
dictionary)
  2) Build UI object which displays and returns type.

* In order to insert a new record with this model:

  1) Insert contact record

* The adhoc query user has one data table.

-----

Granted,  changing the value of contact_type.type would require edits to the
contact records.
It may be possible to add simple syntax to allow editing of a 'recurringchar
dictionary' to
get around isolated problem which would only exist in certain applications.

Actually,  maybe 'dictionary' or 'dictref' would be a better name for the
datatype.



Sat, 7 Jul 2001 13:02:55 -0500
"David Bennett" <dbennett@bensoft.com>
RE: New SQL Datatype RECURRINGCHAR
On Sat, 7 Jul 2001, David Bennett wrote:

> -----
> In a nutshell you are recommending:
> -----
> 
>   create table contact_type (
>     code	  int2,
>     type    char(16),
>     PRIMARY KEY ( code )
>   );
> 
>   create table contact (
>     number  	serial,
>     name    	char(32),
>     type          int2,
>     PRIMARY KEY ( number ),
>     FOREIGN KEY ( type ) REFERENCES contact_type ( code )
>   );
> 
>   create view contact_with_readble_type as (
>     select c.number as number,
>            c.name as name,
>            t.type as type
>     from
>            contact c,
>            contact_type t
>   );
> 
> * To build a type lookup table:
> 
>   1) Select type and code from contact_type
>   2) Build UI object which displays type and returns code
Just 'select distinct' on a view should do just fine. 

> * In order to insert a new record with this model:
> 
>   1) Look up to see if type exists
>   2) Insert new type
>   3) Get type ID
>   4) Insert contact record
This can be encapsulated with "ON INSERT" rule on a view.

> * The adhoc query user is now faced with
>   the task of understanding 3 data tables.
No, only one view. All the logic is encapsulated there.

> 
> -----
> With recurringchar you could do this easily as:
> -----
> 
>   create table contact (
>     number  	serial,
>     name    	char(32),
>     type          recurringchar1,
>     PRIMARY KEY ( number ),
>   );
> 
> * To build a type lookup table:
> 
>   1) Select distinct type from contact (optimized access to recurringchar
> dictionary)
>   2) Build UI object which displays and returns type.
> 
> * In order to insert a new record with this model:
> 
>   1) Insert contact record
> 
> * The adhoc query user has one data table.
> 
> -----
> 
> Granted, changing the value of contact_type.type would require edits
> to the contact records. It may be possible to add simple syntax to
> allow editing of a 'recurringchar dictionary' to get around isolated
> problem which would only exist in certain applications.
> 
> Actually, maybe 'dictionary' or 'dictref' would be a better name for
> the datatype.
These things belong in application or middleware (AKA views/triggers), not
in database server itself.

There are multiple problems with your implementation, for example,
transaction handling, assume this situation:

Tran A inserts a new contact with new type "foo", but does not commit.
Dictionary assigns value of N to 'foo'.

Tran B inserts a new contact with type foo. What value should be entered
in the dictionary? N? A new value? 

If a type disappears from database, does its dictionary ID get reused?

All these questions are not simple questions, and its not up to database
to decide it. Your preferred solution belongs in your triggers/views, not
in core database.



Sat, 7 Jul 2001 21:24:08 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
RE: New SQL Datatype RECURRINGCHAR
This would be a potential feature of being able to insert into views
in general.  Reversing the CREATE VIEW statement to accept inserts,
deletes and updates.

If true, focus on that.  Theres lots of views that cannot be reversed
properly -- unions come to mind -- but perhaps this type of simple
join could be a first step in the package.  I believe this is on the
TODO list already.

Different attack, but accomplishes the same thing within SQL standards
as I seem to recall views are supposed to do this where reasonable.


Failing that, implement this type of action the same way as foreign
keys.  Via the described method with automagically created views,
tables, etc.  Though I suggest leaving it in contrib for sometime.
Enum functionality isn't particularly useful to the majority whose
applications tend to pull out the numbers for states when the
application is opened (with the assumption they're generally static).

--
Rod Taylor

Your eyes are weary from staring at the CRT. You feel sleepy. Notice
how restful it is to watch the cursor blink. Close your eyes. The
opinions stated above are yours. You cannot imagine why you ever felt
otherwise.

----- Original Message -----
From: "Alex Pilosov" <alex@pilosoft.com>
To: "David Bennett" <dbennett@bensoft.com>
Cc: <pgsql-hackers@postgresql.org>
Sent: Saturday, July 07, 2001 9:24 PM
Subject: RE: [HACKERS] New SQL Datatype RECURRINGCHAR


> On Sat, 7 Jul 2001, David Bennett wrote:
>
> > -----
> > In a nutshell you are recommending:
> > -----
> >
> >   create table contact_type (
> >     code   int2,
> >     type    char(16),
> >     PRIMARY KEY ( code )
> >   );
> >
> >   create table contact (
> >     number  serial,
> >     name    char(32),
> >     type          int2,
> >     PRIMARY KEY ( number ),
> >     FOREIGN KEY ( type ) REFERENCES contact_type ( code )
> >   );
> >
> >   create view contact_with_readble_type as (
> >     select c.number as number,
> >            c.name as name,
> >            t.type as type
> >     from
> >            contact c,
> >            contact_type t
> >   );
> >
> > * To build a type lookup table:
> >
> >   1) Select type and code from contact_type
> >   2) Build UI object which displays type and returns code
> Just 'select distinct' on a view should do just fine.
>
> > * In order to insert a new record with this model:
> >
> >   1) Look up to see if type exists
> >   2) Insert new type
> >   3) Get type ID
> >   4) Insert contact record
> This can be encapsulated with "ON INSERT" rule on a view.
>
> > * The adhoc query user is now faced with
> >   the task of understanding 3 data tables.
> No, only one view. All the logic is encapsulated there.
>
> >
> > -----
> > With recurringchar you could do this easily as:
> > -----
> >
> >   create table contact (
> >     number  serial,
> >     name    char(32),
> >     type          recurringchar1,
> >     PRIMARY KEY ( number ),
> >   );
> >
> > * To build a type lookup table:
> >
> >   1) Select distinct type from contact (optimized access to
recurringchar
> > dictionary)
> >   2) Build UI object which displays and returns type.
> >
> > * In order to insert a new record with this model:
> >
> >   1) Insert contact record
> >
> > * The adhoc query user has one data table.
> >
> > -----
> >
> > Granted, changing the value of contact_type.type would require
edits
> > to the contact records. It may be possible to add simple syntax to
> > allow editing of a 'recurringchar dictionary' to get around
isolated
> > problem which would only exist in certain applications.
> >
> > Actually, maybe 'dictionary' or 'dictref' would be a better name
for
> > the datatype.
> These things belong in application or middleware (AKA
views/triggers), not
> in database server itself.
>
> There are multiple problems with your implementation, for example,
> transaction handling, assume this situation:
>
> Tran A inserts a new contact with new type "foo", but does not
commit.
> Dictionary assigns value of N to 'foo'.
>
> Tran B inserts a new contact with type foo. What value should be
entered
> in the dictionary? N? A new value?
>
> If a type disappears from database, does its dictionary ID get
reused?
>
> All these questions are not simple questions, and its not up to
database
> to decide it. Your preferred solution belongs in your
triggers/views, not
> in core database.
>
>
>
> ---------------------------(end of
broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>


Sat, 7 Jul 2001 22:59:41 -0400
"Rod Taylor" <rbt@barchord.com>
Re: New SQL Datatype RECURRINGCHAR

On Sat, 7 Jul 2001, Rod Taylor wrote:

> This would be a potential feature of being able to insert into views
> in general.  Reversing the CREATE VIEW statement to accept inserts,
> deletes and updates.
Definitely not a 'potential' feature, but a existing and documented one.
Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not
automatic, though.

> If true, focus on that.  Theres lots of views that cannot be reversed
> properly -- unions come to mind -- but perhaps this type of simple
> join could be a first step in the package.  I believe this is on the
> TODO list already.
On TODO list are updatable views in SQL sense of word, [i.e. automatic
updateability of a view which matches certain criteria].

> Different attack, but accomplishes the same thing within SQL standards
> as I seem to recall views are supposed to do this where reasonable.
> 
> 
> Failing that, implement this type of action the same way as foreign
> keys.  Via the described method with automagically created views,
> tables, etc.  Though I suggest leaving it in contrib for sometime.
> Enum functionality isn't particularly useful to the majority whose
> applications tend to pull out the numbers for states when the
> application is opened (with the assumption they're generally static).

Original suggestion was not for an enum type, it was for _dynamically
extensible_ data dictionary type. 

ENUM is statically defined, and it wouldn't be too hard to implement, with
one exception: one more type-specific field needs to be added to
pg_attribute table, where would be stored argument for the type (such as,
length for a char/varchar types, length/precision for numeric type, and
possible values for a enum type). 

This just needs a pronouncement that this addition is a good idea, and
then its a trivial thing to implement enum.

-alex


Sat, 7 Jul 2001 23:42:28 -0400 (EDT)
Alex Pilosov <alex@pilosoft.com>
Re: New SQL Datatype RECURRINGCHAR
> > This would be a potential feature of being able to insert into
views
> > in general.  Reversing the CREATE VIEW statement to accept
inserts,
> > deletes and updates.
> Definitely not a 'potential' feature, but a existing and documented
one.
> Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not
> automatic, though.

Trust me, I know how to go about doing those kinds of things manually.
I was referring to the automated reveral -- which creates this
features in a very simple manner without any additions or changes to
system tables -- aside from reverse rules themselves which is a more
generic feature.

> > If true, focus on that.  Theres lots of views that cannot be
reversed
> > properly -- unions come to mind -- but perhaps this type of simple
> > join could be a first step in the package.  I believe this is on
the
> > TODO list already.
> On TODO list are updatable views in SQL sense of word, [i.e.
automatic
> updateability of a view which matches certain criteria].
>
> > Different attack, but accomplishes the same thing within SQL
standards
> > as I seem to recall views are supposed to do this where
reasonable.
> >
> >
> > Failing that, implement this type of action the same way as
foreign
> > keys.  Via the described method with automagically created views,
> > tables, etc.  Though I suggest leaving it in contrib for sometime.
> > Enum functionality isn't particularly useful to the majority whose
> > applications tend to pull out the numbers for states when the
> > application is opened (with the assumption they're generally
static).
>
> Original suggestion was not for an enum type, it was for
_dynamically
> extensible_ data dictionary type.

ENUMs from my memory are easily redefined.  And since the database
they're implemented in requires table locks for everything, they can
appear dynamic (nothing is transaction safe in that thing anyway).


Sat, 7 Jul 2001 23:48:57 -0400
"Rod Taylor" <rbt@barchord.com>
Re: New SQL Datatype RECURRINGCHAR
> > > This would be a potential feature of being able to insert into
> views
> > > in general.  Reversing the CREATE VIEW statement to accept
> inserts,
> > > deletes and updates.
> > Definitely not a 'potential' feature, but a existing and
documented
> one.
> > Read up on rules, especially 'ON INSERT DO INSTEAD' stuff. Its not
> > automatic, though.
>
> Trust me, I know how to go about doing those kinds of things
manually.
> I was referring to the automated reveral -- which creates this
> features in a very simple manner without any additions or changes to
> system tables -- aside from reverse rules themselves which is a more
> generic feature.

Hmm. My above statement lost all credibility in poor grammer and
speeling.  Time for bed I suppose.

Anyway, the point is that some of the simple views should be straight
forward to reversing automatically if someone has the will and the
time it can be done.  A while back a list of 'views which cannot be
reversed' was created and included things such as Unions,
Intersections, exclusions, aggregates, CASE statements, and a few more
items.


Sun, 8 Jul 2001 00:03:12 -0400
"Rod Taylor" <rbt@barchord.com>
Re: New SQL Datatype RECURRINGCHAR
"Rod Taylor" <rbt@barchord.com> writes:
> Anyway, the point is that some of the simple views should be straight
> forward to reversing automatically if someone has the will and the
> time it can be done.  A while back a list of 'views which cannot be
> reversed' was created and included things such as Unions,
> Intersections, exclusions, aggregates, CASE statements, and a few more
> items.

SQL92 has a notion that certain simple views are "updatable", while the
rest are not.  In our terms this means that we should automatically
create ON INSERT/UPDATE/DELETE rules if the view is updatable according
to the spec.  I have not bothered to chase down all the exact details
of the spec's "updatableness" restrictions, but they're along the same
lines you mention --- only one referenced table, no aggregation, no
set operations, all view outputs are simple column references, etc.

My feeling is that the restrictions are stringent enough to eliminate
most of the interesting uses of views, and hence an automatic rule
creation feature is not nearly as useful/important as it appears at
first glance.  In real-world applications you'll have to expend some
thought on manual rule creation anyway.

			regards, tom lane

Sun, 08 Jul 2001 00:47:07 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: New SQL Datatype RECURRINGCHAR 
David Bennett wrote:
> Alex,
>
> I think I fully understand your position. Let me put wrap up our
> conversation so far.
> [lots of arguments for recurringchar]

    All  I've  seen  up  to  now  is  that you continue to mix up
    simplification on the user side with data and content control
    on  the DB designer side.  Do the users create all the tables
    and would have to create the views, or is that more  the  job
    of someone who's educated enough?

    And  about  the multiple lookups and storage of new types, we
    have procedural languages and database triggers.

    This is no personal offense, but I  just  don't  see  why  we
    should  adopt non-standard MySQLism for functionality that is
    available  through  standard  mechanisms   with   alot   more
    flexibility and control.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com


Wed, 11 Jul 2001 08:19:46 -0400 (EDT)
Jan Wieck <JanWieck@Yahoo.com>
Re: New SQL Datatype RECURRINGCHAR