EzDevInfo.com

Good tool to visualise database schema? [closed]

Are there any good tools for visualising a pre-existing database schema? I'm using MySQL if it matters.

I'm currently using MySQL Workbench to process an SQL create script dump, but it's clunky, slow and a manual process to drag all the tables about (which would be okay if it wasn't so slow).

How do I change db schema to dbo

I imported a bunch of tables from an old sql server (2000) to my 2008 database. All the imported tables are prefixed with my username, for example: jonathan.MovieData. In the table properties it lists jonathan as the db schema. When I write stored procedures I now have to include jonathan. in front of all the table names which is confusing.

How do I change all my tables to be dbo instead of jonathan?

Current result: jonathan.MovieData

Desired result: dbo.MovieData

What good are SQL Server schemas?

I'm no beginner to using SQL databases, and in particular SQL Server. However, I've been primarily a SQL 2000 guy and i've always been confused by schemas in 2005+. Yes, I know the basic definition of a schema, but what are they really used for in a typical SQL Server deployment?

I've always just used the default schema. Why would I want to create specialized schemas? Why would I assign any of the built-in schemas?

EDIT: To clarify, I guess i'm looking for the benefits of schemas. If you're only going to use it as a security scheme, it seems like database roles already filled that.. er.. um.. role. And using it a as a namespace sepcifier seems to have been something you could have done with ownership (dbo versus user, etc..).

I guess what i'm getting at is, what do Schemas do that you couldn't do with owners and roles? What are their specifc benefits?

Normalizing Human Skin Colors for User Interaction

A while ago I came across this answer that introduced me to the obscure (at least for me) ISO 5218: a standard for representing human sexes (or is it genders? - thanks @Paul).

For a pet project I'm working on I need my database schema to store the skin color of a person, and I'm wondering if a similar standard exists. All my life I've heard people using terms such as "White", "Caucasian", "Black", "Blonde", "Brunette", "Afro", "Albino" and so on, but after some research in Wikipedia I've realized that everybody is wrong, because those words can all have different meanings:

• White: yeah, it's a color
• Caucasian: defines the race
• Black: yet another color
• Blonde: skin or hair color
• Brunette: again, skin or hair color
• Afro: "hairdo"?!
• Albino: also represents more than the skin color

The Wikipedia has the following about human races:

• Caucasoid
• Congoid
• Capoid
• Mongoloid
• Australoid

Seriously, Mongoloid?! I don't know about the connotations of the English language but in my native language (Portuguese) that's a synonym for a person who suffers from the Down syndrome disorder...

This Wikipedia page also has some interesting additional information:

Johann Friedrich Blumenbach (1752-1840), one of the founders of what some call scientific racism theories, came up with the five color typology for humans: white people (the Caucasian or white race), more or less black people (the Ethiopian or black race), yellow people (the Mongolian or yellow race), cinnamon-brown or flame colored people (the American or red race) and brown people (the Malay or brown race).

The problem with using races (besides the horrific names chosen and scientific racism), is that they don't necessarily represent the skin color of a person... Take the following photo from Wikipedia:

The most serious attempt I could find to classify skin color is the Von Luschan's chromatic scale:

Most people however, are not aware of their von Luschan's scale (myself included). I also though of having the user visually specifying the color of their skin tone but that could lead to some problems due to the different color profiles used by the operating systems / monitors.

There is also a more general von Luschan's scale used to classify sun tanning risk:

1. von Luschan 1-5 (very light).
2. von Luschan 6-10 (light).
3. von Luschan 11-15 (intermediate).
4. von Luschan 16-21 ("Mediterranean").
5. von Luschan 22-28 (dark or "brown").
6. von Luschan 29-36 (very dark or "black").

Since this can become a very sensitive topic for some people I'm wondering what would be the best way to store this information in a normalized database. Is there a correct globally accepted standard to describe skin color without affecting susceptibilities while using straightforward terms and avoiding complicated and unfamiliar definitions such as von Luschan's scale?

Similar standards exist for eye and hair color. How would you approach the skin tone terminology?

How do I show the schema of a table in a MySQL database? [closed]

From the MySQL console, what command displays the schema of any given table?

Why don't most vim color schemes look as nice as the screenshot when I use them? [closed]

I have downloaded many vim color schemas and tried them out, but many of them don't look like the official screenshot.

For example, vim's own color schema - desert should look like this:

But in my vim, many colors won't display, for example the background.

But some color schemas work correctly.

Why is that?

In the: Edit-> Profile Preferences -> Colors, I select the "use colors from system theme"

Does rake db:schema:dump recreate schema.rb from migrations or the database itself?

Does

rake db:schema:dump

recreate schema.rb from migrations or the database itself?

What does elementFormDefault do for XML/When is it used?

So I found some definitions for elementFormQualifed values:

qualified - elements and attributes are in the targetNamespace of the schema

unqualified - elements and attributes do not have a namespace

So from that definition I would think that if a schema is set to qualified then why must you prefix the type with the namespace? And what are the scenarios that you would even have one set to unqualified for that matter? I tried Googling, but all I got were a couple W3C pages that were extremely hard to understand.

This is the file I am working with right now, why do I need to declare the type as 'target:TypeAssignments' when I declare the targetNamespace as the same one as xmlns:target?

<?xml version="1.0" encoding="UTF-8"?>
<schema 
    xmlns="http://www.w3.org/2001/XMLSchema"
    xmlns:target="http://www.levijackson.net/web340/ns"
    targetNamespace="http://www.levijackson.net/web340/ns" elementFormDefault="qualified"
    >

<element name="assignments">
    <complexType>
    	<sequence>
    		<element name="assignments" type="target:TypeAssignments" minOccurs="1" maxOccurs="unbounded"></element>
    	</sequence>
    </complexType>
</element>

<complexType name="TypeAssignments">
    <sequence>
    	<element name="assignment" type="target:assignmentInfo" minOccurs="0" maxOccurs="unbounded"></element>
    </sequence>
</complexType>

<complexType name="assignmentInfo">
    <sequence>
    		<element name="name" type="string"></element>
    		<element name="page" type="target:TypePage"></element>
    		<element name="file" type="target:TypeFile" minOccurs="0" maxOccurs="unbounded"></element>
    </sequence>
    <attribute name="id" type="string" use="required"/>
</complexType>

<simpleType name="TypePage">
    <restriction base="integer">
    	<minInclusive value="50" />
    	<maxInclusive value="498" />
    </restriction>
</simpleType>

<simpleType name="TypeFile">
    <restriction base="string">
    	<enumeration value=".xml" />
    	<enumeration value=".dtd" />
    	<enumeration value=".xsd" />
    </restriction>
</simpleType>

</schema>

Thanks in advance,
Levi

Log4Net "Could not find schema information" messages

I decided to use log4net as a logger for a new webservice project. Everything is working fine, but I get a lot of messages like the one below, for every log4net tag I am using in my web.config:

Could not find schema information for the element 'log4net'...

Below are the relevant parts of my web.config:

  <configSections>
    <section name="log4net" 
        type="log4net.Config.Log4NetConfigurationSectionHandler, log4net" />
  </configSections>
  <log4net>
    <appender name="RollingFileAppender" type="log4net.Appender.RollingFileAppender">
      <file value="C:\log.txt" />
      <appendToFile value="true" />
      <rollingStyle value="Size" />
      <maxSizeRollBackups value="10" />
      <maximumFileSize value="100KB" />
      <staticLogFileName value="true" />
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level: %message%newline" />
      </layout>
    </appender>
    <logger name="TIMServerLog">
      <level value="DEBUG" />
      <appender-ref ref="RollingFileAppender" />
    </logger>
  </log4net>

Solved:

1. Copy every log4net specific tag to a separate xml-file. Make sure to use .xml as file extension.
2. Add the following line to AssemblyInfo.cs:

[assembly: log4net.Config.XmlConfigurator(ConfigFile = "xmlFile.xml", Watch = true)]

nemo added:

Just a word of warning to anyone follow the advice of the answers in this thread. There is a possible security risk by having the log4net configuration in an xml off the root of the web service, as it will be accessible to anyone by default. Just be advised if your configuration contains sensitive data, you may want to put it else where.

@wcm: I tried using a separate file. I added the following line to AssemblyInfo.cs

[assembly: log4net.Config.XmlConfigurator(ConfigFile = "log4net.config", Watch = true)]

and put everything dealing with log4net in that file, but I still get the same messages.

How can I tell jaxb / Maven to generate multiple schema packages?

Example:

</plugin>       
       <plugin>
           <groupId>org.jvnet.jaxb2.maven2</groupId>
           <artifactId>maven-jaxb2-plugin</artifactId>
           <version>0.7.1</version>
           <executions>
             <execution>
               <goals>
                 <goal>generate</goal>
               </goals>
             </execution>
           </executions>
            <configuration>
             <schemaDirectory>src/main/resources/dir1</schemaDirectory>
              <schemaIncludes>
                  <include>schema1.xsd</include>
              </schemaIncludes>
              <generatePackage>schema1.package</generatePackage>
           </configuration>
         </plugin>
          <plugin>
           <groupId>org.jvnet.jaxb2.maven2</groupId>
           <artifactId>maven-jaxb2-plugin</artifactId>
           <version>0.7.1</version>
           <executions>
             <execution>
               <goals>
                 <goal>generate</goal>
               </goals>
             </execution>
           </executions>
            <configuration>
             <schemaDirectory>src/main/resources/dir2</schemaDirectory>
              <schemaIncludes>
                  <include>schema2.xsd</include>
              </schemaIncludes>
              <generatePackage>schema2.package</generatePackage>
           </configuration>
         </plugin>
       </plugins>

What happened: Maven executes the the first plugin. Then deletes the target folder and creates the second package, which then is visible.

I tried to set target/somedir1 for the first configuration and target/somedir2 for the second configuration. But the behavior does not not change? Any ideas? I do not want to generate the packages directly in the src/main/java folder, because these packages are genereated and should not be mixed with manual created classes.

Change Schema Name Of Table In SQL

I want to change schema name of table Employees in Database. In the current table Employees database schema name is dbo I want to change it to exe. How can I do it ?

Example:

FROM

dbo.Employees

TO

exe.Employees

I tried with this query:

ALTER SCHEMA exe TRANSFER dbo.Employees

But this gives me an error:

Cannot alter the schema 'exe', because it does not exist or you do not have permission.

What did I miss?

Creating a multi-tenant application using PostgreSQL's schemas and Rails

Stuff I've already figured out

I'm learning how to create a multi-tenant application in Rails that serves data from different schemas based on what domain or subdomain is used to view the application.

I already have a few concerns answered:

1. How can you get subdomain-fu to work with domains as well? Here's someone that asked the same question which leads you to this blog.
2. What database, and how will it be structured? Here's an excellent talk by Guy Naor, and good question about PostgreSQL and schemas.
3. I already know my schemas will all have the same structure. They will differ in the data they hold. So, how can you run migrations for all schemas? Here's an answer.

Those three points cover a lot of the general stuff I need to know. However, in the next steps I seem to have many ways of implementing things. I'm hoping that there's a better, easier way.

Finally, to my question

When a new user signs up, I can easily create the schema. However, what would be the best and easiest way to load the structure that the rest of the schemas already have? Here are some questions/scenarios that might give you a better idea.

1. Should I pass it on to a shell script that dumps the public schema into a temporary one, and imports it back to my main database (pretty much like what Guy Naor says in his video)? Here's a quick summary/script I got from the helpful #postgres on freenode. While this will probably work, I'm gonna have to do a lot of stuff outside of Rails, which makes me a bit uncomfortable.. which also brings me to the next question.
2. Is there a way to do this straight from Ruby on Rails? Like create a PostgreSQL schema, then just load the Rails database schema (schema.rb - I know, it's confusing) into that PostgreSQL schema.
3. Is there a gem/plugin that has these things already? Methods like "create_pg_schema_and_load_rails_schema(the_new_schema_name)". If there's none, I'll probably work at making one, but I'm doubtful about how well tested it'll be with all the moving parts (especially if I end up using a shell script to create and manage new PostgreSQL schemas).

Thanks, and I hope that wasn't too long!

Laying out a database schema for a calendar application

I want to write a calendar application. It is really recurring items that throw a wrench in the works for the DB schema. I would love some input on how to organize this.

What if a user creates an event, and inputs that it repeats everyone Monday, forever? How could I store all that in the database? I can't create infinite events. Do I simply put a table in there that holds the relevant info so I can calculate where all the events go? If so, I would have to calculate them every time the user views a new part of the calendar. What if they page through the months, but they have a ton of recurring items?

Also, the schema needs to handle when a user clicks an item and says "Edit this one in the sequence" not all items in the sequence. Do I then split the one item off of the sequence?

Update 1

I have not looked at iCal at all. To be clear, I think saving the info that allows you to calculate the recurring items, and splitting off any that differ from the sequence is a great way to store it to be able to transfer it. But I think that in an application, this would be too slow, to do the date math all over the place.

add_column for references (Rails)

I have the following Rails migration which works perfectly (irrelevant pieces removed):

create_table :comments do |t|
  t.text :body
  t.references :post
end

Now I'd like to add an author column to my comments table (which is the userid of a user), but I have no idea how to do it (I'm tempted to just write the MySql-specific syntax using an execute).

I've been looking at add_column here which doesn't mention references. I've actually found TableDefinition#references but I have no idea how to use it with an add_column statement.

Is this possible? Also, is it true that, for MySql, the "references" functionality does not actually establish relationships between the tables?

How do I query if a database schema exists

As part of our build process we run a database update script as we deploy code to 4 different environments (due to crazy business rules, but that's another post). Further, since the same query will get added to until we drop a release into production (yet a few more domains to add to... frustrating); it HAS to be able to run multiple times on a given database. Like this:

IF NOT EXISTS (SELECT * FROM sys.tables WHERE object_id = OBJECT_ID(N'[Table]'))
BEGIN
  CREATE TABLE [Table]
  (...)
END

Currently, I have a create schema statement in the deployment/build script. Where do I query for the existence of a schema?