[RFC] Abstract table definitions

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

[RFC] Abstract table definitions

Daniel Friesen-4
We've got a nice abstract query system but our table creation and  
migrations are horrible. We re-write them for multiple database engines.  
And as a result extensions need to do the same and often don't. Leading to  
things being a mess for databases other than MySQL.

While I was doing the sites stuff a syntax for defining tables came to  
mind and I wrote out an RfC for a method of defining our database  
abstractly.

https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions

--
~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Siebrand Mazeland (WMF)
Hey Daniel,

As far as I know this is something lying on a shelve somewhere (ie probably a branch in core's svn, as, IIRC, Chad, was close I completing this once a year or two ago.

--
Siebrand Mazeland

M: +31 6 50 69 1239
Skype: siebrand

Op 18 aug. 2012 om 19:52 heeft "Daniel Friesen" <[hidden email]> het volgende geschreven:

> We've got a nice abstract query system but our table creation and migrations are horrible. We re-write them for multiple database engines. And as a result extensions need to do the same and often don't. Leading to things being a mess for databases other than MySQL.
>
> While I was doing the sites stuff a syntax for defining tables came to mind and I wrote out an RfC for a method of defining our database abstractly.
>
> https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
>
> --
> ~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]
>
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Chad
In reply to this post by Daniel Friesen-4
We tried this before, and I'd love to see it happen. Take a look at the
abstract-schema branch in SVN for what we did.

-Chad
On Aug 18, 2012 1:53 PM, "Daniel Friesen" <[hidden email]> wrote:

> We've got a nice abstract query system but our table creation and
> migrations are horrible. We re-write them for multiple database engines.
> And as a result extensions need to do the same and often don't. Leading to
> things being a mess for databases other than MySQL.
>
> While I was doing the sites stuff a syntax for defining tables came to
> mind and I wrote out an RfC for a method of defining our database
> abstractly.
>
> https://www.mediawiki.org/**wiki/Requests_for_comment/**
> Abstract_table_definitions<https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions>
>
> --
> ~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]
>
> ______________________________**_________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/**mailman/listinfo/wikitech-l<https://lists.wikimedia.org/mailman/listinfo/wikitech-l>
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Daniel Friesen-4
I see the branch, but I don't see the code:
https://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/phase3/
It looks like someone just copied phase3 and then never actually changed
any code. I don't even see a syntax idea there.

~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]

On 12-08-18 11:10 AM, Chad wrote:

>
> We tried this before, and I'd love to see it happen. Take a look at
> the abstract-schema branch in SVN for what we did.
>
> -Chad
>
> On Aug 18, 2012 1:53 PM, "Daniel Friesen" <[hidden email]
> <mailto:[hidden email]>> wrote:
>
>     We've got a nice abstract query system but our table creation and
>     migrations are horrible. We re-write them for multiple database
>     engines. And as a result extensions need to do the same and often
>     don't. Leading to things being a mess for databases other than MySQL.
>
>     While I was doing the sites stuff a syntax for defining tables
>     came to mind and I wrote out an RfC for a method of defining our
>     database abstractly.
>
>     https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
>
>     --
>     ~Daniel Friesen (Dantman, Nadir-Seen-Fire)
>     [http://daniel.friesen.name]
>
>     _______________________________________________
>     Wikitech-l mailing list
>     [hidden email] <mailto:[hidden email]>
>     https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>

_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Tyler Romeo
I think this is an awesome idea and I'd be willing to help on this.

I have a few questions about the page linked above though:

   - Why don't we use foreign keys in MySQL?
   - The word "hack" is used three times. That's probably an issue. ;)
   - Is there a reason we're changing the default from NULL to NOT NULL?
   Wouldn't it be better to just use a "required" keyword?
   - We should probably support MySQL's -- comment syntax, just in case.
   - What's the [ignore] on the insert call do? (It becomes a little
   confusing since there is also the [tablename] syntax on global indexes.)
   - The Alter Table syntax looks really weird (with tildas and whatnot).


*--*
*Tyler Romeo*
Stevens Institute of Technology, Class of 2015
Major in Computer Science
www.whizkidztech.com | [hidden email]



On Sat, Aug 18, 2012 at 2:20 PM, Daniel Friesen
<[hidden email]>wrote:

> I see the branch, but I don't see the code:
> https://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/phase3/
> It looks like someone just copied phase3 and then never actually changed
> any code. I don't even see a syntax idea there.
>
> ~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]
>
> On 12-08-18 11:10 AM, Chad wrote:
> >
> > We tried this before, and I'd love to see it happen. Take a look at
> > the abstract-schema branch in SVN for what we did.
> >
> > -Chad
> >
> > On Aug 18, 2012 1:53 PM, "Daniel Friesen" <[hidden email]
> > <mailto:[hidden email]>> wrote:
> >
> >     We've got a nice abstract query system but our table creation and
> >     migrations are horrible. We re-write them for multiple database
> >     engines. And as a result extensions need to do the same and often
> >     don't. Leading to things being a mess for databases other than MySQL.
> >
> >     While I was doing the sites stuff a syntax for defining tables
> >     came to mind and I wrote out an RfC for a method of defining our
> >     database abstractly.
> >
> >
> https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
> >
> >     --
> >     ~Daniel Friesen (Dantman, Nadir-Seen-Fire)
> >     [http://daniel.friesen.name]
> >
> >     _______________________________________________
> >     Wikitech-l mailing list
> >     [hidden email] <mailto:
> [hidden email]>
> >     https://lists.wikimedia.org/mailman/listinfo/wikitech-l
> >
>
> _______________________________________________
> Wikitech-l mailing list
> [hidden email]
> https://lists.wikimedia.org/mailman/listinfo/wikitech-l
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Daniel Friesen-4
On 12-08-18 10:37 PM, Tyler Romeo wrote:
> I think this is an awesome idea and I'd be willing to help on this.
>
> I have a few questions about the page linked above though:
>
>   * Why don't we use foreign keys in MySQL?
>
We don't use them now so I didn't use them in the abstract table schema
types. PostreSQL and iirc Oracle use them so I added reference(), it
also acts as a documentation hint. We could regenerate the table schema
documentation based on this. This time around directly extracting what
column refers to another out of the code.
For MySQL, FKeys actually lower performance, but in any case if you
think we should use foreign keys we should have a wikitech-l discussion
over it.

>   * The word "hack" is used three times. That's probably an issue. ;)
>
Heh. Yeah. Though how else do we handle the few spots where a part of
our database schema goes and does something complete out of line from
the rest of the schema?
eg: cl_timestamp uses timestamp instead of our binary(14).

>   * Is there a reason we're changing the default from NULL to NOT
>     NULL? Wouldn't it be better to just use a "required" keyword?
>
95% of our column definitions are NOT NULL. In fact not allowing null is
practically a default, you don't really allow null unless you make an
explicit decision that you want null values.
So I made NOT NULL the default and made allowing null an explicit
nullable flag.
NOT NULL only prevents you from setting a field as null. It doesn't make
it required because most other fields will still default to something
like 0, '', or some other default that's specified. So "required"
doesn't really fit the meaning

>   * We should probably support MySQL's -- comment syntax, just in case.
>
Yeah I planned to from the start. Just include opening #, --, and //
comments inside the parser.
Although, when it came to table alters I started contemplating the --
comment vs. -column; parsing and rethought that.
Though thinking about it again, the parsing will probably work fine.

>   * What's the [ignore] on the insert call do? (It becomes a little
>     confusing since there is also the [tablename] syntax on global
>     indexes.)
>
That was for INSERT IGNORE. After I noticed that some of our
archive/*.sql files use INSERT IGNORE. I dropped the idea of `insert
ignore tablename ...` because it would be ambiguous for a table named
"ignore".
So yeah, it would be nice to find a better syntax for insert ignore.
I'm also contemplating if I should find a syntax for INSERT..SELECT
since we seem to use it in a small number of migrations.

>   * The Alter Table syntax looks really weird (with tildas and whatnot).
>
I started wanting to keep the alter table syntax as close as possible to
the table definition/creation syntax.
So I started with prefixing with - and + which work beautifully as DROP
and ADD column/etc... commands.
Then when I thought of MODIFY/CHANGE I didn't want to make that
unprefixed and ~ seemed to work best for that.

But yeah, here and there it could use some ideas for alternative ways to
do the syntax.

~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]

> *--*
> *Tyler Romeo*
> Stevens Institute of Technology, Class of 2015
> Major in Computer Science
> www.whizkidztech.com
> <http://www.whizkidztech.com/> | [hidden email]
> <mailto:[hidden email]>
>
>
>
> On Sat, Aug 18, 2012 at 2:20 PM, Daniel Friesen
> <[hidden email] <mailto:[hidden email]>> wrote:
>
>     I see the branch, but I don't see the code:
>     https://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/phase3/
>     It looks like someone just copied phase3 and then never actually
>     changed
>     any code. I don't even see a syntax idea there.
>
>     ~Daniel Friesen (Dantman, Nadir-Seen-Fire)
>     [http://daniel.friesen.name]
>
>     On 12-08-18 11:10 AM, Chad wrote:
>     >
>     > We tried this before, and I'd love to see it happen. Take a look at
>     > the abstract-schema branch in SVN for what we did.
>     >
>     > -Chad
>     >
>     > On Aug 18, 2012 1:53 PM, "Daniel Friesen"
>     <[hidden email] <mailto:[hidden email]>
>     > <mailto:[hidden email]
>     <mailto:[hidden email]>>> wrote:
>     >
>     >     We've got a nice abstract query system but our table
>     creation and
>     >     migrations are horrible. We re-write them for multiple database
>     >     engines. And as a result extensions need to do the same and
>     often
>     >     don't. Leading to things being a mess for databases other
>     than MySQL.
>     >
>     >     While I was doing the sites stuff a syntax for defining tables
>     >     came to mind and I wrote out an RfC for a method of defining our
>     >     database abstractly.
>     >
>     >    
>     https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
>     >
>     >     --
>     >     ~Daniel Friesen (Dantman, Nadir-Seen-Fire)
>     >     [http://daniel.friesen.name]
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l
Reply | Threaded
Open this post in threaded view
|

Re: [RFC] Abstract table definitions

Tyler Romeo
In reply to this post by Tyler Romeo
Makes sense. IMHO most of the stuff I mentioned is just minor qualms. The
overall syntax is pretty nice, and I think it'd be a great idea to have
something like this.

*--*
*Tyler Romeo*
Stevens Institute of Technology, Class of 2015
Major in Computer Science
www.whizkidztech.com | [hidden email]



On Sun, Aug 19, 2012 at 7:12 AM, Daniel Friesen
<[hidden email]>wrote:

>  On 12-08-18 10:37 PM, Tyler Romeo wrote:
>
> I think this is an awesome idea and I'd be willing to help on this.
>
>  I have a few questions about the page linked above though:
>
>    - Why don't we use foreign keys in MySQL?
>
>  We don't use them now so I didn't use them in the abstract table schema
> types. PostreSQL and iirc Oracle use them so I added reference(), it also
> acts as a documentation hint. We could regenerate the table schema
> documentation based on this. This time around directly extracting what
> column refers to another out of the code.
> For MySQL, FKeys actually lower performance, but in any case if you think
> we should use foreign keys we should have a wikitech-l discussion over it.
>
>
>
>    - The word "hack" is used three times. That's probably an issue. ;)
>
>  Heh. Yeah. Though how else do we handle the few spots where a part of
> our database schema goes and does something complete out of line from the
> rest of the schema?
> eg: cl_timestamp uses timestamp instead of our binary(14).
>
>
>
>    - Is there a reason we're changing the default from NULL to NOT NULL?
>    Wouldn't it be better to just use a "required" keyword?
>
>  95% of our column definitions are NOT NULL. In fact not allowing null is
> practically a default, you don't really allow null unless you make an
> explicit decision that you want null values.
> So I made NOT NULL the default and made allowing null an explicit nullable
> flag.
> NOT NULL only prevents you from setting a field as null. It doesn't make
> it required because most other fields will still default to something like
> 0, '', or some other default that's specified. So "required" doesn't really
> fit the meaning
>
>
>
>    - We should probably support MySQL's -- comment syntax, just in case.
>
>  Yeah I planned to from the start. Just include opening #, --, and //
> comments inside the parser.
> Although, when it came to table alters I started contemplating the --
> comment vs. -column; parsing and rethought that.
> Though thinking about it again, the parsing will probably work fine.
>
>
>
>    - What's the [ignore] on the insert call do? (It becomes a little
>    confusing since there is also the [tablename] syntax on global indexes.)
>
>  That was for INSERT IGNORE. After I noticed that some of our
> archive/*.sql files use INSERT IGNORE. I dropped the idea of `insert ignore
> tablename ...` because it would be ambiguous for a table named "ignore".
> So yeah, it would be nice to find a better syntax for insert ignore.
> I'm also contemplating if I should find a syntax for INSERT..SELECT since
> we seem to use it in a small number of migrations.
>
>
>
>    - The Alter Table syntax looks really weird (with tildas and whatnot).
>
>  I started wanting to keep the alter table syntax as close as possible to
> the table definition/creation syntax.
> So I started with prefixing with - and + which work beautifully as DROP
> and ADD column/etc... commands.
> Then when I thought of MODIFY/CHANGE I didn't want to make that unprefixed
> and ~ seemed to work best for that.
>
> But yeah, here and there it could use some ideas for alternative ways to
> do the syntax.
>
>
> ~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]
>
>  *--*
> *Tyler Romeo*
> Stevens Institute of Technology, Class of 2015
> Major in Computer Science
> www.whizkidztech.com | [hidden email]
>
>
>
> On Sat, Aug 18, 2012 at 2:20 PM, Daniel Friesen <[hidden email]
> > wrote:
>
>> I see the branch, but I don't see the code:
>>
>> https://svn.wikimedia.org/viewvc/mediawiki/branches/abstract-schema/phase3/
>> It looks like someone just copied phase3 and then never actually changed
>> any code. I don't even see a syntax idea there.
>>
>> ~Daniel Friesen (Dantman, Nadir-Seen-Fire) [http://daniel.friesen.name]
>>
>>  On 12-08-18 11:10 AM, Chad wrote:
>> >
>> > We tried this before, and I'd love to see it happen. Take a look at
>> > the abstract-schema branch in SVN for what we did.
>> >
>> > -Chad
>> >
>> > On Aug 18, 2012 1:53 PM, "Daniel Friesen" <[hidden email]
>>  > <mailto:[hidden email]>> wrote:
>> >
>> >     We've got a nice abstract query system but our table creation and
>> >     migrations are horrible. We re-write them for multiple database
>> >     engines. And as a result extensions need to do the same and often
>> >     don't. Leading to things being a mess for databases other than
>> MySQL.
>> >
>> >     While I was doing the sites stuff a syntax for defining tables
>> >     came to mind and I wrote out an RfC for a method of defining our
>> >     database abstractly.
>> >
>>  >
>> https://www.mediawiki.org/wiki/Requests_for_comment/Abstract_table_definitions
>> >
>> >     --
>> >     ~Daniel Friesen (Dantman, Nadir-Seen-Fire)
>> >     [http://daniel.friesen.name]
>>
>
_______________________________________________
Wikitech-l mailing list
[hidden email]
https://lists.wikimedia.org/mailman/listinfo/wikitech-l