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" <lists@nadir-seen-fire.com mailto:lists@nadir-seen-fire.com> 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
Wikitech-l@lists.wikimedia.org <mailto:Wikitech-l@lists.wikimedia.org>
https://lists.wikimedia.org/mailman/listinfo/wikitech-l

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/ | tylerromeo@gmail.com mailto:tylerromeo@gmail.com

On Sat, Aug 18, 2012 at 2:20 PM, Daniel Friesen <lists@nadir-seen-fire.com mailto:lists@nadir-seen-fire.com> 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"
<lists@nadir-seen-fire.com <mailto:lists@nadir-seen-fire.com>
> <mailto:lists@nadir-seen-fire.com
<mailto:lists@nadir-seen-fire.com>>> 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]