Skip to content

# Tag: common_schema

## common_schema: looking for contributions

MySQL common_schema Open Source July 20, 2011

In my announcement for common_schema I have failed to deliver the following message:

> I will be happy to receive contributions to common_schema, and I will be happy to have contributors on this project

#### What kind of contributions are wanted?

So, I’m mostly interested right now in:

- Views: providing more insight on metadata (data types, schemata, volumes, transactions, locks, connections, etc.)
- Views: SQL code generation (e.g. generate my FOREIGN KEYs)
- Functions: DETERMINISTIC, NO SQL, preferably no control flow inside, making for simple calls what is usually a typical, repeating function combination.
- Tables: small, static data that is handy to have around. But really, small.

Less interested in:

- Procedures
- OS dependent solutions (e.g. calling LOAD_FILE(‘/proc/meminfo’) which only makes sense on Linux)

I’ve got a few ideas already, and have received contributions and ideas from others (I’m looking at you, Roland).

If you have a good idea you’d like to share, why don’t you submit it under the Issues page (submit as “Request for new component”)

Everything is done on my spare time, and there’s not much of that. So, if you do submit something and it’s not in right away, please be patient. Thanks!

## Announcing common_schema: common views & routines for MySQL

MySQL Analysis common_schema Data Types Development Indexing INFORMATION_SCHEMA InnoDB Monitoring Open Source Schema Security SQL Stored routines July 13, 2011

Today I have released common_schema, a utility schema for MySQL which includes many views and functions, and is aimed to be installed on any MySQL server.

#### What does it do?

There are views answering for all sorts of useful information: stuff related to schema analysis, data dimensions, monitoring, processes & transactions, security, internals… There are basic functions answering for common needs.

Some of the views/routines simply formalize those queries we tend to write over and over again. Others take the place of external tools, answering complex questions via SQL and metadata. Still others help out with SQL generation.

Here are a few highlights:

- Did you know you can work out simple monitoring of your server with a query? There’s a view to do that for you.
- How about showing just the good parts of the processlist?
- Does your schema have redundant keys?
- Or InnoDB tables with no PRIMARY KEY?
- Is AUTO_INCREMENT running out of space?
- Can I get the SQL statements to generate my FOREIGN KEYs? To drop them?
- And can we finally get SHOW GRANTS for all accounts, and as an SQL query?
- Ever needed a 64 bit CRC function?
- And aren’t you tired of writing the cumbersome SUBSTRING_INDEX(SUBSTRING_INDEX(str, ‘,’, 3), ‘,’, -1)? There’s an alternative.

There’s more. Take a look at the common_schema documentation for full listing. And it’s evolving: I’ve got quite a few ideas already for future components.

Some of these views rely on heavyweight INFORMATION_SCHEMA tables. You should be aware of the impact and risks.

#### What do I need to install?

There’s no script or executable file. It’s just a schema. The distribution in an SQL file which generates common_schema. Much like a dump file.

#### Continue reading » “Announcing common_schema: common views & routines for MySQL”