|
Softpanorama
(slightly skeptical)
Open Source Software Educational Society |
May the
source be with you,
but remember the KISS principle ;-)
|
Perl Scripting for Open Source
Databases
The best collection of links to start is not this page but
Pal's Linux RDBMS Library.
My page is pretty rudimentary and currently not maintained
well as my current work and research interests are more in OS security area.
But still it was useful for my students before and might be useful for somebody
now. So I decided to keep it on the WEB.
Taking into account a poor shape of most imbed database
languages, Linux+Apache+MySQL+ PHP combination (or Linux+Apache+PostgreSQL+PHP)
at least for WEB applications looks comparative to proprietary offerings.
Using Python is the other possibility. I feel that adding procedural components
to SQL is a stupid idea and the only reasonable way to go is to use a decent
scripting language. Java is another possibility and it has strong
typing. Although it is almost an industry standard (Cobol 2000 ;-), and
I would like to warn you that IMHO it's not about scripting and thus is
neither provide high productivity typical for scripting language, no high
performance typical for compiled languages (it's getting better). A lot
of organizations spend millions of dollars on Java interfaces with databases
with marginal or oven negative results as for development or support costs.
They would get the same or better result much quicker and for a fraction
of money with any decent scripting language.
If you need really large database I would see first if the
database design is implementable under
PostgreSQL. for historical reason I prefer Perl, but this is for only
historical reasons. For smaller databases MySQL is OK or in case you work
with WEB might even be a better bet (more literature, more open applications
to study, more people to consult about problems, etc). In case those two
are for some reason unsuitable Oracle on Solaris is a safe (but extremely
expensive) solution. Oracle on Linux is a questionable bet -- it you are
paying those huge money for the database you need a decent hardware and
the best OS the money can buy -- that means UltraSparc III and Solaris 9.
As an embeddable database Berkeley DB is an excellent solution.
I generally prefer
PostgreSQL which is free in true sense of the word (it is licensed under
BSD license), but of course you will be better off buying support
if you are doing something serious unless you want to became a PostreSQL
developer. But MySQL is a slightly stronger bet if we are talking
about <Web-server>>-<scriptinglanguage>-<database>
troika. Actually apache-PHP-MySQL is a very strong development
platform.
Note: Please remember that for large projects
changing databases is a very painful experience that should be avoided as
much as possible.
Again, this page should be viewed only after
Pal's Linux RDBMS Library:
This site is a compilation of the best free online
readings about relational databases on Linux. If you're a Linux RDBMS/database
administrator, a database designer/developer, or simply a Linux user
with database ambitions, you'll find links to valuable resources here:
articles, papers, and books on various aspects of relational database
management. Needless to say, much of this material is more or less applicable
to other (UNIX) environments, too.
Good luck !
Dr. Nikolai Bezroukov
Notes:
- This is a Spartan WHYFF (We Help
You For Free) site written by people for whom English
is not a native language.
Some amount of grammar and spelling errors should be
expected.
- The site contain some broken links
as it develops like a living tree...
Please try to use Google, Open directory,
etc. to find a replacement link (see
HOWTO search the WEB for details). We would appreciate
if you can
mail us a correct link.
|
|
|
|
About: Ora2Pg is a Perl module to export an Oracle database schema
to a PostgreSQL compatible schema. It connects your Oracle database, extracts
its structure, and generates an SQL script that you can load into your PostgreSQL
database. It dumps the database schema (tables, views, sequences, indexes,
grants) with primary, unique, and foreign keys into PostgreSQL syntax without
editing the SQL code generated. It also dump Oracle data into PostgreSQL
DB as online process or into a file. You can choose what columns can be
exported for each table.
Changes: This release add extraction of a function-based index
that appears as SYS_NC, and a new configuration option to not export alter
sequence after data export
Most of the applications I've worked with over the years have been
enterprise applications requiring the management of lots of data.
Development teams working on such projects
often treat the database as a completely separate entity from the application.
This sometimes stems from an organizational structure that separates
the database team from the application-development teams. Other times,
it's simply what teams are used to doing. Either way, I've found that
this separation leads to some of the following practices (or lack thereof):
- Manually applying changes to the database
- Not sharing database changes with other members of the team
- Inconsistent approaches to applying (database or data) changes
- Ineffective manual mechanisms for managing changes from one
database version to the next
These are inefficient practices that leave developers out of sync
with data changes. Moreover, they can cause the application's users
to experience problems with inconsistent or corrupt data.
Figure 1 illustrates the manual approach that's often used on software
development projects. Manual changes are often inconsistently applied
and error prone, and they can make it difficult to undo what's already
been done or analyze the history of database changes over time. For
example, a DBA might remember to apply changes to the lookup data on
one occasion, but a developer might forget to insert this data later
into the same table.
You can avoid the manual approach's pitfalls
by implementing a database change strategy that minimizes human intervention.
Through a combination of practices and tools, you can use a consistent
and repeatable process for applying changes to your database and data.
In this article, I'll cover:
- Using a tool called LiquiBase to migrate between database versions
- How to run database migrations automatically
- Practices for consistently applying database changes
- Applying database refactorings using LiquiBase
If you are familiar with SQL, then the small relational database
language (SRDL) Sergei presents here will seem like an old friend. SRDL
is designed to provide your applications with flexibility in dealing
with database problems.
Sergei is a developer for Counselware in Montreal, Canada. He
can be contacted at savs@CS.McGill.CA
When developing software, we are often confronted with what appears
to be a typical database problem, yet ends up requiring operations some
database engines can't deliver. Obviously, what we need in such situations
is a tool that lets us quickly create custom, specialized features.
The tool I present in this article -- a small relational database language
called SRDL -- is designed to provide applications with a considerable
amount of flexibility in dealing with database problems. If you are
familiar with SQL, you will find a lot of similarities in SRDL. There
are differences between the two languages, of course, primarily in the
definition of relations, structure of operators, and typing scheme.
SRDL is implemented in C++ (using template classes). The current
implementation is about 2500 lines of code. It should compile under
GCC and most other modern C++ compilers, and run on practically any
platform. The implementation can either be used interactively or embedded
into C++ applications that call member functions implementing the operators
explicitly. The complete SRDL source code (distributed as freeware)
is available electronically; see "Programmer's Services," page 3.
There are two distinguishable levels in the SRDL implementation --
the lower-storage level and the upper-manipulation level. There is a
minimal interface between the two, and all algorithmic complexity of
operators is in the upper level, with the lower level providing a way
to read or write into some particular format on some storage. Multiple
implementations of the lower level are possible, so that relations can
be stored in different formats (or even in different storage areas;
for example, placed into memory to speed up execution of consecutive
operators).
|
Victor R. Rivarola S.
- Subject: MySQL? Not even in the race. ( Apr 24, 2007, 23:09:27
) |
Don't beleive me? Make a file named mysql.sql
and feed it into mysql:
CREATE TABLE A
(ID INT NOT NULL PRIMARY KEY
);
CREATE TABLE B
(ID INT NOT NULL PRIMARY KEY
,A INT NOT NULL REFERENCES A(ID)
);
-- These statements should fail because of
-- referential integrity.
INSERT INTO B (ID,A) VALUES (5,5);
INSERT INTO A (ID) VALUES (5);
-- If we got this far, we can already laugh
at this "database"'s referential integrity.
-- Now lets check data type safety.
INSERT INTO B (ID,A) VALUES ('A',7);
-- MySQL and SQLite both finish these statements
succesfully, showing that any referal to them as database should
be enough to make anybody knowleadgable die of laugher.
-- Now, let us test the contents of both A
and B.
SELECT * FROM A:
SELECT * FROM B;
Run this script if you dare on a MySQL server.
It will spit on your face:
ID
5
ID A
5 5
0 7
Feed it like this:
cat mysql.sql | mysql database_name
Note that if you copy the script to the clipboard
and then paste it back into the MySQL console, it will at least
give you a warning when you try to insert 'A' into ID, but it
will go on happily and do it. Better, but not enough.
Any database that will let you execute those
commands in that order is not fit for a children's toy.
MySQL does, Postgres doesn't. Microsoft Access
doesn't either, so it is a better database than MySQL.
Yes, I know about
InnoDB. However, it is not the default table type.
Instead, MyISAM piece of junk is.
Which means that of all the people who use MySQL, very few actually
use InnoDB. So, argument is moot.
Besides, to use InnoDB you would need to add
a "TYPE=InnoDB" after the closing parenthesis and before the
final semicolon. This totally non-standard "feature" (MS style).
Besides how does this helps the type check
misfeature? It doesn't. Once again you must resort to
a nonstandard, nondefault, totally unknown, mode change command
to fix it.
I would end up calling MySQL a toy, but even
that would be WAY too generous.
As for the fact of its popularity, if you
believe that fallacious argument you would have to conclude
the obviously untrue statement that Microsoft Windows
XP is much, much, much better than Linux.
|
Make your MySQL server fly with these server tuning tips
07 Jun 2007 (IBM DeveloperWorks)
Applications using the LAMP (Linux®, Apache, MySQL, PHP/Perl) architecture
are constantly being developed and deployed. But often the server administrator
has little control over the application itself because it's written
by someone else. This
series of three articles discusses many of the server configuration
items that can make or break an application's performance. This third
article, the last in the series, focuses on tuning the database layer
for maximum efficiency.
"We are announcing today that there will be no VFP 10"
We have been asked about our plans for a new version of VFP. We are
announcing today that there will be no VFP 10. VFP9 will continue to
be supported according to our existing policy with support through 2015
(http://support.microsoft.com/lifecycle/?p1=7992).
We will be releasing SP2 for Visual FoxPro 9 this summer as planned,
providing fixes and additional support for Windows Vista.
Additionally, as you know, we’ve been working on a project codenamed
Sedna for the past year or so. Sedna is built using the extensibility
model of VFP9 and provides a number of new features including enhanced
connectivity to SQL Server, integration with parts of the .NET framework,
support for search using Windows Desktop Search and Windows Vista as
well as enhanced access to VFP data from Visual Studio.
Concurrently, the community has been using CodePlex (http://www.codeplex.com)
to enhance VFP using these same capabilities in the
VFPx and
VFPy projects. Some of these
community driven enhancements include:
- Support for GDI+
- An enhanced class browser
- Support for Windows Desktop Alerts
- An object oriented menu system
- Integration with MSBuild
- A rule-based code analysis tool similar to fxCop in Visual Studio
- An Outlook Control Bar control
To reiterate, today we are announcing that we are not planning on
releasing a VFP 10 and will be releasing the completed Sedna work on
CodePlex at no charge. The components written as part of Sedna will
be placed in the community for further enhancement as part of our shared
source initiative. You can expect to see the Sedna code on CodePlex
sometime before the end of summer 2007.
The VFP team
Microsoft Visual FoxPro Roadmap
The VFP team made a special announcement to the community on
March 13, 2007. The team has announced that there will be no VFP 10.
VFP 9 will continue to be supported as per the support policy (http://support.microsoft.com/lifecycle/?p1=7992)
through 2015.
After the release of Visual FoxPro 9.0 and Visual FoxPro 9.0 Service
Pack1, the Visual FoxPro team at Microsoft has been working on a new
project code-named Sedna. Sedna takes advantage of enhancements in Visual
FoxPro 9.0. The primary goal of Sedna is to expand on the ability of
Visual FoxPro-based solutions to better integrate with other Microsoft
products and technologies.
Features in Sedna will target Visual FoxPro interoperability with
application components created using Visual Studio 2005, the .NET Framework
2.0 and SQL Server 2005. Sedna will also help improve the ability for
Visual FoxPro 9.0 solutions to be successfully deployed on Windows Vista.
Sedna is planned to be released before the end of Summer of 2007.
Additional information can be found in the transcript of theVisual
FoxPro DevCon 2005 Interview with Alan Griver and Ken Levy from
June 2005, as well as the September 30th 2005 audio podcast
FoxShow #24: Interview with Ken Levy - both interviews discuss details
of the Visual FoxPro Roadmap. The complete SouthwestFox 2005 keynote
slide deck on the Visual FoxPro Roadmap and community news now online,
1.6MB PPT download
SouthwestFox2005_Keynote.ppt.
There are two pillars for the main themes of Sedna,Interoperability
and Extensibility. Some of the extensibility enhancements will
target report system features like additional report output file types.
For interoperability, the areas (in priority) including the following
upcoming Microsoft products and technologies:
- .NET Framework 2.0 with Visual Studio 2005
- SQL Server 2005 and SQL Server 2005 Express
- Windows Vista
- Windows Communication Foundation (code named Indigo)
- Windows Presentation Foundation (code named Avalon)
Sedna Feature Overview documents
describe the various aspects of Sedna.
Visual FoxPro will also release a service pack that includes several
critical fixes, including those required to better support Windows Vista.
As indicated in prior public statements, Microsoft does not plan
to merge Visual FoxPro into Visual Studio .NET, nor are there plans
to create a new Visual FoxPro .NET programming language. Visual
FoxPro will remain stand-alone Win32 based, and will run on 64-bit Windows
in 32-bit compatibility mode.
For more information about Visual FoxPro, including answers toVisual
FoxPro FAQ, go to the
Visual FoxPro Developer Center Web site. at
http://msdn.com/vfoxpro.
(Updated: March 2007)
Not true, but interesting :-)
"Microsoft has announced that it will
open-source the core portions of the Visual FoxPro DBMS software
to its CodePlex community development site. At the same time, Microsoft
has announced that it will no longer be making new versions of the FoxPro
DBMS."
Comment
Microsoft will be releasing the completed Sedna work on CodePlex
at no charge. The components written as part of Sedna will be placed
in the community for further enhancement as part of our shared source
initiative.
You can expect to see the Sedna code on CodePlex sometime before the
end of summer 2007.
NOTE that the released part is Sedna
and NOT VFP nor VFP core elements!
Sedna is a project Microsoft has been working on for the past year or
so. Sedna is built using the extensibility model of VFP9 and provides
features like better connectivity to SQL Server, integration with parts
of the .NET framework, wrappers for Vista APIs to make it easier to
write applications that run on Vista machines, as well as better support
for VFP data in Visual Studio.
The VFP Community Message is at:
http://msdn2.microsoft.com/en-us/vfoxpro/bb308952.aspx
Thanks!
Sun used to provide slightly cheaper support then Red Hat. Oracle undercut
this price so this advantage is lost. Still Solaris stands on its own as
an enterprise OS (and it serves as Oracle standard 64-bit platform) and
in this role it competes more with AIX and HP-UX then Linux (although X86-64
changed that). So this development might hurt Sun a little bit on
low end but it is not applicable to midrange and high-end server were Oracle
mainly used. Actually IBM AIX is the major Sun's competitor in his space
due to Power 5 CPUs scalability. But all three major Unix vendors have sizable
Oracle deployment (surprisingly HP-UX is a strong contender in this space
and many large corporations use HP-UX to run Oracle).
According to WimCoekaerts, Oracle's director of Linux engineering,
Oracle's own production servers are
rolled out with Linux -- not Solaris -- and
Linux is now the de facto standard platform
for 9,000 Oracle developers.
Sun is quick to point out the technical advantages of Solaris over
Linux, and to be fair, they are numerous; score a point for Sun. What's
more, Solaris is open source, just like Linux.
... ... ...
Sun says that, when you run the numbers, Red Hat's subscription pricing
is expensive compared with what you get with a Solaris license. Oracle
apparently agrees, because its baseline Linux support contract will
be priced at half what Red Hat charges.
In short, whatever the effect Oracle's
Unbreakable Linux has on Red Hat, it will also have a heavy impact
on Sun.
For the list of systems (currently approximately a dozen) see
Oracle Validated Configurations
August 14, 2006 Oracle Validated
Configurations are pre-tested, validated architectures with software,
hardware, storage and networking components together with documented
best practices for deployment. Oracle and its strategic partners offer
and recommend these configurations to enable end-users to deploy fully
tested solutions to achieve standardization with high performance, scalability
and reliability while lowering infrastructure costs.
... ... ....
Oracle is seeing significant end-user demand for Linux x86-64 architectures
and is fully committed to developing, advancing
and promoting the 64-bit commodity Linux. All new chipsets
and servers are now being shipped with x86-64 architecture, thereby
offering a much wider hardware selection to end-users than some of the
other architectures. Therefore, Oracle has
chosen to initially make Oracle Validated Configurations available on
Linux x86-64.
Oracle is involved with Linux, Coekaerts says, foremost because Oracle
uses Linux. A lot of Linux. Right now almost 10,000 Linux servers are
in use internally at Oracle. Essentially, every production server at
Oracle is a Linux server. In addition, about 9,000 developers at Oracle
are using Linux to develop products.
A lot of that can be attributed to one
simple factor: cost savings. "We use Linux for the same reason all the
other companies are using Linux," Coekaerts says.
But there's more to it than that. The Oracle database is a large,
complex application that places a lot of demands on the underlying OS.
When Oracle wants to experiment, changing how the OS works to optimize
database performance, it's easier to do with an open source, community-driven
OS than a proprietary one. Hence the number of Linux kernel contributions
from Oracle engineers; as a fast research and prototyping tool, Linux
can't be beat.
The end result of all this in-house Linux experience is a whole lot
of in-house expertise. In a way, then, it was only natural for Oracle
to enter into the Linux support business. It's not widely recognized,
but Oracle has provided enterprise Linux support through its
Unbreakable Linux program for about four years. Now, with
its new Oracle Validated Configurations initiative, it is poised to
take that a step further.
An Oracle Validated Configuration is
essentially what it sounds like. Oracle and its partners have selected
specific combinations of hardware and software -- including server hardware,
chip sets, Linux OSes, drivers, and storage -- and subjected them to
approximately 60 to 70 tests designed to tax each system to the limits
of its performance. The Validated label means you're getting a complete
system that has been fully configured, certified, and optimized to run
Oracle, down to specific kernel module parameters.
The Oracle stamp of approval doesn't just benefit Oracle users. Because
Oracle is such a heavyweight application, it tends to highlight problems
more quickly than other kinds of software. A system that runs Oracle
well is almost guaranteed to run other applications well.
By comparison, Coekaerts says it typically takes customers nine to
12 months to get full server stacks properly configured when they do
it themselves. "We're saving lots of people's time, including our own,"
he says.
But the bigger picture is one of perception. Over the long term,
Coekaerts would like to see Oracle recognized for the contributions
it has made to Linux throughout the years. As the world's second-largest
software company, Oracle's influence over the industry isn't going away,
but its reputation as an outsider in the world of open source just might.
"We're doing Linux the way we should be doing it," Coekaerts says.
"We're trying to use our influence to do something good."
On October 25, 2006 Oracle announced its own support for a clone of
Red Hat. It is evident that Oracle will eat Red Hat lunch, but this is also
a severe blow to Suse (making Novell almost irrelevant in enterprise Linux
space). Some people think that this is a revenge for Jboss, but big
business is not about retribution. Still details are pretty interesting
and somewhat damaging for Solaris at mid-range: Sun should be very careful
with licensing and cost of support to avoid the falloff...
Currently, Red Hat only provides bug fixes for the latest version
of its software. This often requires customers to upgrade to a new version
of Linux software to get a bug fixed. Oracle's new Unbreakable
Linux program will provide bug fixes to future, current, and back releases
of Linux. In other words, Oracle will provide the same level
of enterprise support for Linux as is available for other operating
systems.
Oracle is offering its Unbreakable Linux program for substantially
less than Red Hat currently charges for its best support. "We believe
that better support and lower support prices will speed the adoption
of Linux, and we are working closely with our partners to make that
happen," said Oracle CEO Larry Ellison. "Intel is a development partner.
Dell and HP are resellers and support partners. Many others are signed
up to help us move Linux up to mission critical status in the data center."
"Oracle's Unbreakable Linux program is available to all Linux
users for as low as $99 per system per year," said Oracle President
Charles Phillips. "You do not have to be a user of Oracle software to
qualify. This is all about broadening the success of Linux. To get Oracle
support for Red Hat Linux all you have to do is point your Red Hat server
to the Oracle network. The switch takes less than a minute."
"We think it's important not to fragment the market," said Oracle's
Chief Corporate Architect Edward Screven. "We will maintain compatibility
with Red Hat Linux. Every time Red Hat distributes a new version we
will resynchronize with their code. All we add are bug fixes, which
are immediately available to Red Hat and the rest of the community.
We have years of Linux engineering experience. Several Oracle employees
are Linux mainline maintainers."
SQLite is a small C library that implements a self-contained, embeddable,
zero-configuration SQL database engine. The primary benefits of using
SQLite is that you can create a self-contained database in your
application.
What is so great about this? Well, for starters you can:
- Manipulate data inside your program using standard SQL construct.
- Zero database configuration – but you get database-like capabilities
for your program.
- Simple, easy to use
API.
You can actually use SQLite with just 3 main API.
- Self-contained: no external dependencies.
- Faster
than popular client/server database engines for most common operations.
With SQLite, it is possible for you to distribute single application
binary, with almost complete database and SQL querying capabilities.
Compared to another embedded database such Berkeley DB, which offers
just key and value assignment, SQLite is much more versatile and easy
to use. You can just use all the SQL syntax that you have become familiar
with to manipulate your data. The data itself is stored inside a single
disk file on your local file-system, which can grow to a maximum of
2 terabytes (241 bytes) in size. For more information, visit
SQLite site.
Under Ubuntu, to install SQLite, issue the following command:
sudo apt-get install sqlite3 libsqlite3-dev libsqlite3-0 sqlite3-doc
|
Oracle played a big card when it bought
InnoDB, the most popular way to inject data
into the open source mySQL database.
Monday mySQL responds by getting
Solid™ Information Technology, a proprietary
database vendor, to take its solidDB Storage
Engine for MySQL open source, under the
GPL, starting in June.
Solid has its base in telecommunications
and transaction processing, which had been
considered a completely different market
from the small fry mySQL supplies. It has
3 million copies out at places like Alcatel,
Cisco, EMC, HP, NEC, Nokia, and Nortel.
The addition of Solid technology to mySQL,
the company said, puts mySQL into the enterprise
league and makes it a direct threat to Oracle.
But does it? After all, Solid is in that
enterprise market, albeit a niche within
it. Solid is not going away, and this is
supposed to be a complementary deal.
So I talked to Paola Lubet, vice president
of marketing for Solid, She told me her
14-year old company had been looking for
a way into the broader enterprise market
for some time, and sees open source as a
"go to market" opportunity.
"Our decision at the moment is to go
into the open source track and use mySQL
as a channel. So we’re going to make available
code that works only with mySQL. On the
side we have a proprietary line of products."
Going to open source with mySQL was also
a comfortable decision for Solid.
Both companies were founded in Finland,
and still do most development there. The
U.S. arms of both companies are in the same
building in Cupertino.
Stacey Quandt, research director for
the
Aberdeen Group in Boston, told me the
deal also opens new markets to mySQL. "For
years mySQL has defined itself as not being
a competitor (in the enterprise space),
but with a transactional engine that gap
can be narrowed," she said.
Web 2.0 projects built on mySQL can also
move ahead with confidence they’re ready
as they move into transactions and as users
"rush to the rail" to support them.
"There’s more work to be done, but the
gap is narrowing," Quandt said of mySQL.
"What I would look for in terms of feature
initiatives and measuring success is for
mySQL to now get into telco and manufacturing
and other verticals." And for Solid? "The
network effects of open source may help
them grow their installed base."
You might say that Oracle’s open source
problems are far from…Finnished.
A recent
interview has DBAs talking about the merits of the open source PostgreSQL
database management system (DBMS) as compared to Oracle – and their
opinions truly run the gamut.
In that interview, Robert Treat and Jason Gilmore, co-authors of
"Beginning PHP and PostgreSQL 8: From Novice to Professional," said
that PostgreSQL 8.0 is much more than just a back end for Web sites.
In many situations, the authors say, PostgreSQL can be used instead
of or as a complement to Oracle and other DBMSs.
DBAs responding to the interview said they liked the low cost of
the open source database, while others said that Oracle's rich feature
set is second to none.
Jim Allen, a longtime Oracle professional and an independent technology
consultant, says he has had considerable experience with PostgreSQL
7.4, but not the newest version, 8.1.
Allen believes that PostgreSQL is much more suitable for the
casual database developer, such as Java developers who need a back end
for [Java
Database Connectivity] access.
"PostgreSQL has a solid set of features now that includes most if
not all of what these developers would ever use," Allen said. "Oracle
has a feature set several orders of magnitude more rich, but few if
any of these features would ever be used by this group."
Another thing Allen likes about PostgreSQL is the fact that the stored
procedure compilation is transactional.
"You can recompile a stored procedure on a live system, and only
transactions starting after that compilation will see the changes,"
he said. "Transactions in process can complete with the old version.
Oracle just blocks on the busy procedure."
Matt S., a DBA, said that he has successfully used PostgreSQL 8 in
conjunction with Microsoft SQL Server 2000 and 2005, as well as with
Oracle 10g.
"I was impressed thoroughly with the ease of implementation, as well
as compatibility and installation of PostgreSQL, considering its open
source nature," he said. "Using it in enterprise applications [and]
Web site situations was relatively painless [and] a simplified security
structure made it very appealing."
Another user, who did not want to be identified, said that
it makes more sense to compare PostgreSQL to OracleXE, a slimmed down
and free version of the Oracle DBMS. The user said that like
PostgreSQL, OracleXE is easy to install and use. He added that OracleXE
includes Apex/HTMLDB to help developers quickly build and deploy Web
applications.
"I've used PostgreSQL in the past [and] it is fine," the user wrote.
"However, Oracle is stepping up and making a pretty good product and
making it easier for organizations to upgrade their database systems
down the road from the free version to their other versions of their
product."
Richard Goulet, a senior Oracle DBA with a New England-area power
components manufacturer, said that he uses Oracle and PostgreSQL side-by-side
for numerous tasks.
Goulet agrees that PostgreSQL is easy to use, and he says it complies
with the SQL standard nicely. He adds that there is plenty of support
readily available for PostgreSQL through numerous mailing lists. But
that's where Goulet's fondness for the open source software ends.
PostgreSQL doesn't behave as nicely as Oracle when the system fills
up, Goulet said. In those instances, the system tends to crash quickly.
Goulet said that setting up a TCP/IP connection capability with PostgreSQL
is hardly an intuitive process. To do it, he says, one needs to modify
the postgres.conf and pg_hba.conf files manually.
"The last big thing between PostgreSQL and Oracle that's really missing
is a gateway product from Oracle. These two don't talk to each other
except by externally built and most times [highly customized] connectors,"
Goulet said. "An Oracle gateway to PostgreSQL would mean a lot to those
who use both products happily."
Josh Berkus, who works with the PostgreSQL Project Core Team, said
there are in fact third party tools available on the Web which help
integrate Oracle and PostgreSQL data. They include Ora2pg and DBI-Link,
he said.
Within the past two years, Oracle, IBM and Microsoft have all released
freely available versions of their flagship database servers, a move
that would have been unheard of just a few years ago. While their respective
representatives would argue the move was made in order to better accommodate
the needs of all users, it's fairly clear that continued pressure from
open source alternatives such as
MySQL and
PostgreSQL have caused these database juggernauts to rethink their
strategies within this increasingly competitive market.
While PostgreSQL's adoption rate continues to accelerate, some
folks wonder why that rate isn't even steeper given its impressive array
of features. One can speculate that many of the reasons for
not considering its adoption tend to be based on either outdated or
misinformed sources.
In an effort to dispel some of the FUD (fear, uncertainty, and doubt)
surrounding this impressive product, instead, I'll put forth several
of the most commonplace reasons companies have for not investigating
PostgreSQL further.
Reason #1: It doesn't run on Windows
PostgreSQL has long supported every modern Unix-compatible operating
system, and ports are also available for
Novell NetWare and
OS/2. With the 8.0 release, PostgreSQL's support for all mainstream
operating systems was complete, as it included a native Windows port.
Now, you can install the PostgreSQL database on a workstation or
laptop with relative ease, thank to an installation wizard similar to
that used for installing Microsoft Word or Quicken.
Reason #2: No professional development and administration tools
Most users who are unfamiliar with open source projects tend to think
DB administrators manage them entirely through a series of cryptic shell
commands. Indeed, while PostgreSQL takes advantage of the powerful command-line
environment, there are a number of graphical-based tools available for
carrying out tasks such as administration and database design.
The following list summarizes just a few of the tools available to
PostgreSQL developers:
- Database modeling: Several commercial and open source products
are at your disposal for data modeling, some of which include
Visual Case and
Data Architect.
- Administration and development: There are numerous impressive
efforts going on in this area, and three products are particularly
promising.
pgAdmin III has a particularly long development history and
is capable of handling practically any task ranging from simple
table creation to managing replication across multiple servers.
Navicat PostgreSQL offers features similar to pgAdmin III and
is packaged in a very well-designed interface. A good, Web-based
tool is
phpPgAdmin.
- Reporting: PostgreSQL interfaces with all mainstream reporting
tools, including
Crystal Reports,
Cognos ReportNet, and the increasingly popular open source reporting
package
JasperReports.
Reason #3: PostgreSQL doesn't support my language
 |
| Proprietary vendors' free
databases: |
| Database heavyweights IBM,
Microsoft and Oracle have all recently released
free versions of their products. More information
about the respective products can be found by
navigating to the following links:
|
|
|
|
|
|
Today's enterprise often relies on an assortment of programming languages,
and if the sheer number of PostgreSQL API contributions available are
any indication, the database is being used in all manner of environments.
The following links point to PostgreSQL interfaces for today's most
commonly used languages:
C++,
C#,
JDBC,
Perl,
PHP,
Python,
Ruby and
Tcl.
Interfaces even exist for some rather unexpected languages, with
Ada,
Common Lisp and
Pascal all coming to mind.
Reason #4: There's nobody to blame when something goes wrong
The misconception that open source projects lack technical support
options is curious, particularly if one's definition of support does
not involve simply having somebody to blame when something goes wrong.
You can find the answers to a vast number of support questions in
the official PostgreSQL manual, which consists of almost 1,450 pages
of detailed documentation regarding every aspect of the database, ranging
from a synopsis of supported data types to system internals.
The documentation is available for
online perusal and downloading in PDF format. For more help, there
are a number of newsgroups accessible through Google groups, with topics
ranging across performance, administration, SQL construction, development
and general matters.
If you're looking for a somewhat more immediate response, hundreds
of PostgreSQL devotees can be found logged into IRC (irc.freenode.net
#postgresql?).
You can plug in to IRC chat clients for all common operating systems
(Windows
included) at any given moment. For instance, on a recent Wednesday
evening, there were more than 240 individuals logged into the channel.
Waking up the next morning, I found more than 252 logged in, including
a few well-known experts in the community. The conversation topics ranged
from helping newcomers get logged into their PostgreSQL installation
for the first time to advanced decision tree generation algorithms.
Everyone is invited to participate and ask questions no matter how simplistic
or advanced.
For those users more comfortable with a more formalized support environment,
other options exist.
CommandPrompt Inc.'s PostgreSQL packages range from one-time incident
support to 24x7 Web, e-mail and phone coverage. Recently,
Pervasive Software Inc. jumped into the fray, offering various support
packages in addition to consulting services. Open source services support
company
SpikeSource Inc. announced PostgreSQL support last summer, along
with integration of the database into its
SpikeSource Core Stack.
Reason #5: You (don't) get what you (don't) pay for
To put it simply, if you require a SQL standards-compliant database
with all of the features found in any enterprise-class product and capable
of storing terabytes of data while efficiently operating under heavy
duress, chances are PostgreSQL will quite satisfactorily meet your needs.
However, it doesn't come packaged in a nice box, nor will a sales representative
stand outside your bedroom window after you download it.
For applications that require Oracle to even function properly, consider
EnterpriseDB, a version of PostgreSQL, which has reimplemented features
such as data types, triggers, views and cursors that copy Oracle's behavior.
Just think of all the extra company coffee mugs you could purchase with
the savings.
... Using the open-standard
JDBC interface, Savvica ported its data to DB2 Express-C from MySQL
in less than a day, said Green.
Defections such as
Savvica's hearten big commercial database vendors, including Microsoft,
Oracle, IBM and even Sybase, which have all released free "express"
databases in the past six months.
Despite more robust
features, these hugely profitable databases have in recent years lost
mind share -- and, increasingly, customers -- to their open-source counterparts.
MySQL AB's success has epitomized the corporate revolt against the license
and support fees charged for commercial databases.
But the free express
databases are "significantly challenging the conventional wisdom about
commercial vs. open-source databases," said Peter O'Kelly, an analyst
at Burton Group.
The commercial database
vendors are opening a second front by adding support for application
frameworks popular with open-source users. On Tuesday, Zend Technologies
released software that enables developers to write applications interacting
with the Oracle database in the PHP scripting language.
"IBM and Oracle are
doing something similar to what MySQL has done: win the hearts and minds
of developers by giving them easier access to technologies," said Mike
Pinette, Zend's vice president of business development.
It's early, and the
success of the big commercial database vendors at wooing back software
developers -- who wield increasing influence over corporate buying decisions
-- is not yet clear.
In the area of database
instructional book sales, considered a good indicator of developer interest,
sales of SQL Server how-to books have surpassed MySQL books this year,
according to Roger Magoulis, director of research at leading publisher,
O'Reilly Media. He believes that interest is due more to the general
release of SQL Server 2005 last fall, rather than just its free edition
-- especially as sales of Oracle or DB2 how-to books have not increased
significantly since the release of their free versions.
Sybase says its Adaptive
Server Enterprise 15 express edition has been downloaded 45,000 times
since its September release, with "a lot of that converting into business,"
according to Marty Beard, Sybase's senior vice president of corporate
development and marketing.
Microsoft, which released
its first free database, MSDE, back in 1999, did not immediately provide
the number of downloads of SQL Server 2005 Express, which was released
last October. But Oracle said hundreds of thousands of developers and
students have downloaded Oracle XE since its beta release that same
month. IBM's DB2 Express-C was made generally available only in late
January.
In contrast, the latest
5.0 version of MySQL has been downloaded more than 6 million times since
October, said Zack Urlocker, vice president of marketing at the Cupertino,
Calif.-based firm. "Sure, the express versions are free, but they come
with very significant limitations, especially the lack of support,"
Urlocker said. "No enterprise customer will go into production with
a database that cannot be supported."
MySQL user Andy Meadows
said he hasn't been tempted to switch.
"Unless it's a large
CRM or identity management system, I've found MySQL to be robust and
scalable enough," said Meadows, president of Live Oak Interactive Inc.,
an Austin-based Web development and hosting firm. And while he acknowledges
that "you can do quite a bit within the parameters" of the express databases,
he fears that vendors will pressure him to upgrade to an expensive supported
version of their database.
Rajeev Kaula, a professor
in Missouri State University's information systems department, said
Oracle XE is easier to install than earlier "lite" Oracle databases
and helps teach students to program more efficiently.
"Students who honed
their skills on MySQL and PHP tend to treat databases only as a way
of storing tables," Kaula said. Learning on Oracle XE, "they are realizing
the power of transferring the business logic to the database itself."
The company has taken its Database 10g Express
Edition (XE), a stripped-down, free version of its flagship database
software, to general availability.
XE is exiting
beta mode, where it received strong global support from several
hundred thousand Java, .NET, PHP and Web developers and students.
Programmers use it to write database
applications on Windows and Linux platforms, Oracle said in a statement.
For example, Oracle said Missouri State
University computer science students use XE in their classes to get
a better feel for the Oracle database through procedures, functions
and triggers.
The software is built on the same code
base as Oracle Database 10g Release 2 and is compatible with all of
Oracle's database family of products. Oracle intended XE to have a simple
upgrade path for users who wish to upgrade and get the bells and whistles
associated with Database 10g standard and enterprise editions.
Database XE is available now on 32-bit
Windows and a slew of Linux operating systems: Debian, Mandriva Linux
2006 Power Pack+, Novell's SUSE Linux Enterprise Server 9 and SUSE Linux
10, Red Hat Enterprise Linux 4, Red Hat Fedora and Ubuntu.
The software can be downloaded for free
here.
Most Perl users are familiar with using
Perl to talk to databases. Perl's DBI is, along with ODBC and JDBC,
one of the most common and widely ported database client interfaces.
The DBI driver for PostgreSQL,
DBD::Pg,
is very well-maintained, and quite featureful. For example,
it recently acquired proper support for prepared statements. Previously,
the client library had emulated these, but with the latest DBD::Pg and
PostgreSQL distributions, you can get real prepared queries, which can
lead to big performance gains in some cases.
However, there is another way of using
Perl with PostgreSQL--writing little Perl programs that actually execute
inside of the server. This way of using Perl is less well known than
using the DBI driver, and is, as far as I know, unique to PostgreSQL.
It lets you do some very cool things that you just can't do in the client.
CA is selling its
Ingres database
technology to private equity firm Garnett & Helfrich Capital, which
is forming a new company to develop and market the open-source software.
Does anyone care? Where will Ingres
get it's market share from? Enterprise DB is sort of a different
animal and MySQL has such a huge user base it doesn't seem like
a great business move to launch yet another open source RDBMS database...especially
when Ingres isn't widely adopted in the enterprise and doesn't have
a niche that will get it in the door. Maybe I am missing something?
At last years LinuxWorld I moderated
panel of open source database company executives, including a guy
from CA who unfortunately became the target for everyone, especially
me, to attack. That was largely based on the fact that CA was treating
it as some noble offering to the community. But I still think Ingres
is an also-ran product without a big market.
Posted by Dave
Rosenberg on November 7, 2005 11:02 AM |
TrackBack (0)
I disagree with your comments. Being
the original database and enhanced to the level it is Ingres is
the most advance database in the market. It has a large customer
base who are loyal to Ingres. Running a small website is fine
one can use MySQL or anything else for that, but if you are doing
something serious, uou need a database which is as serious as Ingres.
Ingres scales really well, has great
backup and recovery, can work on multiple platforms, installs in
a flash and the best part is that with Ingres being such a mature
product you do not have to worry about stability of the system.
MySQL 5 is just coming out with some
very important database features which Ingres has had for years.
Ingres is the "real" database at
fraction of the cost of Oracle, but languished due to lack of promotion.
With G&H pumping in so much resources on it, I believe it can be
the best out there leaving MySQL far behind.
Cascade is a Web-based content management
system. It's based around the idea of organizing resources into a hierachy
of categories, much like Yahoo. Some features include generating static
or dynamic HTML, allowing user comments on ratings on resources, design
abstraction through templates, suggested addition and update management,
an auto-generated "What's New" page, support for related categories
and virtual subcategories, and more. It can use Postgres or MySQL as
its SQL database.
Postgresql AutoDoc has the ability to
output XML, which can be loaded into Dia to create a UML diagram of
the database (complete with table relations and descriptive information),
an HTML form for further detailed information, GraphViz .dot output,
and Docbook 4.1 style SGML for inclusion with project documentation
as an appendix. It works on any 7.x PostgreSQL-based database.
The libsite-db-perl module provides basic
and easy database-connectivity through both URLs (like 'postgres://user:password@myhost/mydatabase')
and standard DBD-parameters. This method should be useful for both beginners
and experts since it decreases the duplication of code and its nice
URL-based access.
AllCommerce is an e-commerce/content
application based on Perl and SQL92 databases which runs under Unix/Linux/Win2000
using SQL database engines (MySQL/Postgres/etc). In addition to a shopping
cart, it provides tools for content, merchandise, statistics, vendor,
order, and inventory. Its modular design allows it to be used as a complete
or partial solution.
dSQL is an SQL query tool for MySQL, Oracle, Postgres, MS- SQL, ODBC
drivers, and all supported Perl DBI drivers. It uses Glade and GTK-Perl.
At last summer’s Linux World expo, Computer
Associates International Inc. (CA) unveiled plans to open up the source
code to its Ingres R/3 database.
Ingres is used by thousands of customers,
but (these days) it’s far from a relational-database powerhouse. With
this in mind, CA sought to sweeten the pot, announcing a total of $1
million in prizes to encourage programmers to develop open-source tools
to migrate applications and data from DB2, Oracle, and other databases
to Ingres R/3.
Last week, CA announced the results of
its Ingres challenge. The winners were determined by a panel that included
Robin Bloor (of consultancy Hurwitz and Associates), JBoss architect
Gavin King, and Ingres founder Dr. Michael Stonebraker. Qualifying entries
had to ensure that applications running on Oracle, IBM, and other databases
could interoperate unmodified with Ingres.
By all accounts, the winning entries—called
Shift2Ingres, EzyMigrate, and DbConverter—do so splendidly. What’s more,
they also amount to a victory of sorts for the Indian subcontinent.
Two of the three winning entries were submitted by programming teams
based in India (New Delhi and Kerala, respectively). The third, for
the record, was written by Bipin Prasad, a programmer from New York.
Shift2Ingres, which took home the biggest
prize ($400,000) is a schema-, data-, and application-migration toolset
for Oracle. It’s based on a Java GUI that lets DBAs configure and perform
the migration of tables and underlying data, views, grants, sequences,
PL/SQL procedures and functions, triggers and other schema objects from
Oracle to Ingres r3 databases.
EzyMigrate, one of the two $300,000 winners,
is a database-migration tool for SQL Server. It uses ODBC to connect
to discover database tables resident in SQL Server and displays table
definitions to end users by means of a Web-based front end. DBAs can
use the interface to make modifications to the table definitions, and
the tool itself creates and populates the tables in the target database.
DBAs can use EzyMigrate to selectively perform individual table migrations,
but the tool also provides models for several different data migration
scenarios, including drop-and-replace, delete-and-replace, and append.
The final winner, DbConverter, also took
home a $300,000 purse. The database-migration program can convert tables,
views, synonyms, indexes, triggers, constraints, groups, roles, users,
permissions, sequences and other schema components from MySQL. DbConverter
uses a Java-based UI that lets DBAs select not just which components
they want to use for a migration, but also what kind of migration they
want to perform—e.g., directly into the target database or by means
of generating a SQL script and relevant output files so they can perform
the migration externally. The tool also supports plug-ins that can be
written to parse and convert associated applications.
"Oracle is the latest database vendor
to put its weight behind the PHP scripting language for business, with
a new tool that integrates PHP applications with its databases.
"Oracle and PHP tool developer Zend Technologies
have developed a PHP engine called Zend Core for Oracle. The tool, to
be released for free in the summer, will integrate Oracle's databases
and Zend's PHP environment..."
Oracle: Open source hucksters and challenge
Oracle Vice President of Technology Marketing
Robert Shimp, whose company is among the only database providers not
trending toward open source in some way, was critical of some open source
moves by database makers in an interview with NewsForge. Shimp did not
name names, but he noted that while Oracle welcomes the market growth
and competition from open source databases, much of the open source
database noise is centered on "orphanware."
Shimp cites companies "using open source because
they see it as a marketing mechanism -- a tool for creating hype or
awareness of older products. This is 'orphanware' -- software
they want to abandon that has no real commercial value, so they put
it out and see what happens."
Shimp elaborated by dividing open source database
strategies into two categories: "serious" open source databases that
provide transparency, allowing developers and users to learn and share;
and the "hucksters" putting out abandonware.
In terms of competition from open source,
Shimp said Oracle views the other databases as an asset in bringing
new database users to the market, calling Oracle's biggest competitor
the filing cabinet. Quite often, users are introduced to databases
through a free or open source database, then move to Oracle as their
needs become greater, according to Shimp, who called innovation Oracle's
challenge and advantage.
"I'm confident we'll be able to create cool things
that will get people to use Oracle," he said. "But I love the challenge
the open source guys are providing."
IBM: Seeding the market
IBM program manager Les King, who touted Big Blue's
move to open its Cloudscape database through the Apache incubator project
Derby,
said he took exception to the idea that Cloudscape was a case of abandonware.
"It had a very thriving life on its own before we decided to open source
it," King said.
He also indicated that like Oracle, IBM sees open
source as a way to gain more market share by catering to developers
with open source databases.
"There is an opportunity for vendors offering
base code to hopefully seed their own market," King said. "Certainly,
if we consider the seeding play, you need something to seed and Derby
is perfectly set up to start seeding DB2 Express."
While there is
talk of IBM open sourcing parts of its full-featured, enterprise-class
DB2, King discounted the idea, referring to the complexity and value
of the code.
"If you take the multiple millions of lines of
code [in DB2], it naturally doesn't lend itself to dumping all of that
code out there," King said. "In addition, today, there is a lot of intellectual
property in the software we sell, and we wouldn't want to make it all
open source."
King did anticipate more open source moves from
more database players, however.
"I think you'll start to see more choice as companies
do take pieces of software and make it open source because that's what
they're targeting," King said, referring to developers. "It doesn't
seem to be slowing."
IBM and Oracle say they are happy to see open
source bring new users to the overall database market, but OS database
players say that their users prefer to stick with open source, which
is now rivaling DB2 and Oracle, even at the higher level.
CA: Commodity competition
Computer Associates Senior Vice President of Development
Tony Gaughan referred to a sort of seeding, indicating his company --
which this year
released its Ingres database under its own
Trusted Open Source License, sees open source as a chance to increase
mindshare and foster innovation by collaborating with its community.
Gaughan, who said the Ingres open sourcing is
a return to the database's "roots" as an open source project at UC Berkeley,
also pointed to the database as another instance of commoditization.
"Customers do not set out to buy a database, they
purchase an application that requires a database," Gaughan said.
"We have seen a need and demand for an enterprise-class,
open source database solution," Gaughan said. "MySQL is suited to read-only
operations and serving up HTML content; PostgreSQL has a much richer
feature set but has scalability problems and doesn't have a company
behind it providing enterprise-level support; Ingres has a mature, proven,
scalable transactional database, and includes clustering, peer-to-peer
replication, and distributed query support."
In response to calls that the CA Trusted Open
Source License is not OSI-approved and the Ingres moves are half-hearted,
Gaughan said the elements not included in the available source were
B1 security and the spatial object library for the database, which CA
does not own.
"The security component that was removed was B1
security, which is a level of security used exclusively in situations
of national security and is only available on B1 secure operating systems
such as Sun CMW," Gaughan said. He added CA is working on a new 3-D,
OpenGIS-compliant spatial object solution the company plans to develop
with its community.
PostgreSQL: Others are too little too
late
PostgreSQL
core team member Josh Berkus said the open source moves by other companies
are both a marketing play and done for technical reasons.
"I think that recent events in databases have
vindicated the idea that open source will continue to spread through
the software world, annexing one sector at a time," Berkus said in an
email. "It's not a question of if software companies will need an open
source strategy, but when."
While it might have been a competitive concern
if Sybase and CA were making Linux and open source moves three years
ago, Berkus indicated it is now the larger, older companies that are
at risk from open source progress.
"My feeling is that both of these are good examples
of proprietary software companies worried about being left behind by
open source," Berkus said.
Berkus said databases are a likely part of the
software stack to go open source because they are infrastructure software,
making it easier to attract developers. However, Berkus questioned whether
Sybase or CA could reap the same benefits as would a database that was
born open source or free.
"In the case of CA, I'm going to reserve judgment
until its license is approved by the Open Source Institute," Berkus
said. "Right now, it looks like more PR than substance; unlike IBM,
CA has not made Ingres separate from CA product management, which means
that they're rather unlikely to attract developers. Compare the failure
of both Borland's Interbase and SAP's SAP-DB as open source projects
-- no offense to
Firebird
DB, which became a dynamic project after they forked it away from
Borland. CA seems to be repeating the same mistakes."
As for Sybase, Berkus said the likely reason the
competitor released the free version for Linux was a
Software Development
Magazine survey, which suggested PostgreSQL was pushing Sybase out
of the market. Sybase did not respond to that contention.
Berkus said regardless of what other vendors are
doing, the open source databases are catching up and in some instances
surpassing the proprietary competition.
"PostgreSQL and even MySQL have surpassed Sybase
in several areas, even if we lag behind in others," Berkus said. "PostgreSQL
is particularly a threat to Sybase because our very robust, fully ACID
transaction support, high availability, and support for custom statistical
functions and complex queries make PostgreSQL perfectly suitable for
a variety of financial applications."
MySQL: Everyone wants to be a toy
For
MySQL CEO Marten
Mickos, the free and open source database cavalcade from the old veterans
is welcome news, and a validation that open source databases are now
competing at the highest levels.
"We think it is good news for users, and we welcome
these products to the open source world, Ingres, and the Linux world,
Sybase," Mickos said. "We have predicted for some time that this would
happen. It validates the MySQL business model. Two years ago, people
said MySQL was a toy. Now, apparently everyone wants to be a toy!"
Mickos said the open source trend among
databases is because of a combination of things, and is also, "a typical
reaction from a large company that would like an older product to become
more popular."
"Some years ago, Borland did the same thing with
Interbase, and later they withdrew from the open source world," Mickos
said.
However, the MySQL chief did refer to the
MaxDB
database, formerly known as SAP DB, as an example of older, closed code
that was successfully nurtured with open source.
"MaxDB may be the only DBMS that started as closed
source and was later successfully open sourced," Mickos said. "SAP AG
open sourced it some years ago, and today, our company is the open source
and commercial channel for MaxDB. It is a very robust, enterprise-level
database and it powers an increasing number of SAP R/3 applications
all over the world. It is also being used more and more by cost-conscious
enterprises, by government agencies, and in developing economies. So
here you have a great example that a DBMS with a long history can indeed
enjoy new growth."
Sybase: Sidestepping corporate approval
While it has not released its Adaptive Server
Enterprise (ASE) database as open source, Sybase has scored success
with the
free Linux version of its database. Citing the same database stepping
stone theory as others, Sybase Senior Group Marketing Manager Amit Satoor
said companies are struggling with the switch from free and open source
databases to enterprise-class databases.
"Most of the customers just want low-cost access
to software so they can start projects that they can deploy on a scalable
platform," Satoor said.
Sybase sought to strengthen its Linux database
recently with the
announcement that its ASE database would run on IBM's eServer OpenPower
server, a prominent Linux deployment based on the Power 5 processor.
Sleepycat: Feeling pressure from open
source
Berkeley DB open source database maker Sleepycat Software's Vice
President of Marketing Rex Wang said moves by Sybase, CA, and IBM were
the older players' reactions to inroads from the open source newcomers.
"There's no doubt that these proprietary database
vendors are being pressured to do this by the success of open source
vendors," Wang said, referring to a Sybase statement that the free Linux
ASE was intended to compete directly with the open source databases,
as well as DB2 and Oracle. "The fact that large, incumbent, proprietary
players have been motivated to make these moves indicates that the momentum
is real."
Wang said Sybase, for example, felt tremendous
price pressure and therefore made a restricted version of its product
free to the most price-sensitive segment of its market. "Their hope
is to get people to try it for free, then sell them the unrestricted
version as they scale their use," he said.
But not so fast, Wang indicated, as Berkeley DB's
developer focus and relative maturity -- in the market for eight years
-- mean it is already appropriate for mission-critical use.
Open source getting good enough
Yankee Group senior analyst Dana Gardner said
the use of a free Linux or open source database to introduce customers
to a wider range of products that scale up to enterprise is a legitimate
strategy. However, Gardner also said the databases that have been open
source since the start may benefit from an evolving, total open source
solution.
"What will be interesting is if the full stack
of open source components becomes some kind of de facto standard," Gardner
said. "In a best-of-breed open source approach, what are the databases
that are part of that de facto standard?"
Gardner added that while he does not see open
source databases such as MySQL and PostgreSQL dethroning the dominant
Oracle and DB2 databases, the capabilities of the open source databases
are quickly catching up and are also sufficient for many higher-level
users.
"MySQL and PostreSQL -- those are quite full-featured,"
Gardner said. "If they continue that trajectory, good enough is good
enough for many people."
FutureSQL is a Rapid Application Development
web database administration tool written in Perl. FutureSQL allows one
to easily setup config files to view, edit, delete and otherwise process
records from a MySQL database. It uses a data dictionary, configuration
files and html templates, and allows "pre-processing" and "post-processing"
on both fields, records and operations. It allows multiple views and
operations on a data set, including the use of joined tables for queries
and reports.
A demo application with most of the features
is included.
By Robert Westervelt, News Writer
12 Aug 2004 | SearchDatabase.com |
Oracle, Microsoft and IBM will respond
to a flood of interest in open source databases by slashing prices and
ramping up automation features, according to "DBMS: Foundation of application
Infrastructure," a market report issued by Cambridge, Mass.-based Forrester
Research Inc.
Proprietary database management system
(DBMS) vendors are beginning to feel the pinch from MySQL, Computer
Associates International Inc.'s Ingres and other open source DBMS vendors
that are attracting new customers with a low cost, no-frills systems.
"Right now we're seeing the impact of
open source databases in the entry-level database arena," said Noel
Yuhanna, a senior analyst at Forrester. "As open source vendors add
new features and functionality, the adoption rate [of open source databases]
will increase."
While more than 80% of enterprises continue
to focus on the top-tier DBMS products -- such as Microsoft SQL Server,
IBM DB2 and Oracle -- for mission-critical database applications, open
source products are accounting for more low-end, small scale deployments,
according to the Forrester report.
Most of the open source DBMS deployments
are for non-mission-critical applications, but Forrester predicts that
more than 20% of overall deployments will be mission-critical by 2006.
Forrester surveyed DBAs, chief information
officers and other IT personnel at 85 North American firms that use
or plan to use open source software. Of those, 52% said that they use
or plan to use MySQL DBMS. Those surveyed said they were lured by low
cost of support and maintenance, low acquisition costs and easier integration
with customized software.
"Open source is clearly making a dent
in database low-end deployments today," Yuhanna said. "I expect larger
scale deployments in the near future."
For now companies are downloading the
free open source versions, initially to test out the functionality in
their specific environment, Yuhanna said. Once enterprises get through
the initial stages of their testing models, most will make the switch
to the fully supported version.
"Enterprises that are serious about open
source will purchase the supported version," Yuhanna said.
MySQL is the leader in open source systems,
but CA announced plans in May to open up the source code to its Ingres
DBMS. In addition, CA said last week it would offer $1 million to encourage
development of an open source database migration toolkit.
"We're seeing very good comments about
Ingres in terms of performance, scalability and feature sets," Yuhanna
said. "It's definitely going to be an important open source database
and will compete on the deployment adoption rate with MySQL."
Meanwhile, IBM is also jumping on board,
announcing recently that it would contribute its Cloudscape Java database,
which it acquired from Informix, to the Apache Software Foundation.
The project is called "Derby," and amounts to more than 500,000 lines
of Java code.
While open source databases begin to
take more market share, Forrester said it is seeing increased interest
in mobile and XML databases. A Sybase subsidiary, iAnywhere Solutions
Inc. dominates the mobile space with more than 65% of the market.
XML-enabled databases supported by Oracle,
IBM, and Microsoft also continue to grow, according to Forrester. The
current market size is about $250 million and is likely to grow to $400
million by 2007, Forrester said.
By: Michael Dortch, Robert
Frances Group
The
LinuxWorld
Conference and Expo in San Francisco last week featured many interesting
announcements. However, the show was at least as noteworthy for its
implications as for its actual news announcements.
IBM Corp.'s announced
transformation of its Cloudscape database solution into an Open Source
offering follows closely the announcement of a similar transformation
for Computer Associates
International, Inc. (CA)'s Ingres database solution. Between Cloudscape
and Ingres, Open Source developers now have multiple new and powerful
options for building more "enterprise-class" solutions.
CA also announced actual availability of Ingres r3 as an Open Source
offering. The company also announced a million-dollar challenge/inducement
to developers who build tools that help users migrate to Ingres from
other proprietary and Open Source database solutions.
Other software vendors announced, discussed, and/or failed to deny or
dispute rumored plans to transform other proprietary solutions into
Open Source offerings. Clearly, the typing is on the display for many
of these vendors. They understand that they need to deliver Open Source
complements and alternatives to their proprietary solutions, or complements
and alternatives priced like Open Source offerings, to remain competitive.
No IT executive desirous of continued employment is going to be able
to justify paying proprietary-level prices for applications without
strongly compelling value propositions, when supporting operating environments
become increasingly cheap or free.
But no IT executive similarly incented is going to argue to "rip and
replace" all proprietary solutions with Open Source alternatives. The
enterprise software market, therefore, looks more and more like a striated,
multi-tiered arena, analogous in some ways to the current market for
broadcast content.
Today, there is "free" broadcast content subsidized rather speculatively
by advertising, the effectiveness of which is impossible to track perfectly.
There is partly subsidized content, paid for by combinations of grants
and subscriptions paid for by only a portion of all consumers. Finally,
there is completely subsidized content, for which consumers pay via
subscriptions or per use.
Soon, there are likely to be three similar tiers of the enterprise software
market. There will be free software, offered in the hopes that it will
generate follow-on demand for fee-based enhancements, services, and/or
support. There will be proprietary software, priced, sold, and supported
much as it is today. In addition, there will likely be a rapidly growing
middle tier of enterprise software solutions that are priced aggressively
and built atop free and inexpensive Open Source foundations, including
but not limited to Linux.
This likely means expanded choices for developers and enterprises alike.
However, environments made up of various combinations of solutions from
all three tiers will definitely require comprehensive, integrated management.
A key question is, therefore, from what vendors will such management
solutions come?
The ability of the leading traditional IT management vendors to answer
this question apparently varies widely. BMC Software, Inc., which won
a Best of Show award at LinuxWorld in 2003, announced no new solutions
or reaffirmations of its commitment to Linux support in San Francisco
last week. This is in marked contrast to CA,
Hewlett-Packard Co.
(HP), and IBM. CA, in addition to the announcements discussed above,
has repeatedly stated publicly that it is encouraging eventual development
of an entire Open Source IT management suite. Regarding HP and IBM,
there were no specific HP OpenView or IBM Tivoli announcements at LinuxWorld.
However, both HP and IBM were prominent at the show, and very willing
to discuss how their respective management arms are committed to making
Linux and Open Source solutions safe bets for the enterprise.
Veritas Software Corp.
, a vendor known primarily for storage management, reminded LinuxWorld
Expo attendees that it had been shipping Linux solutions since 1999.
The company also reinforced its new position as a provider of solutions
intended to enable utility computing. Veritas also announced offerings
intended to enable rapid migration between Linux and other environments,
and that it had joined the
Open Software Development
Labs, Inc.
Veritas is transforming and expanding its core mission by focusing specifically
on making enterprise IT architectures ready for Linux and Open Source
solutions. IT executives should expect to hear lots of other vendors
tout similar strategies - and be prepared to carefully separate promise
from reality.
RFG believes IT management solution vendors must quickly and firmly
declare and demonstrate willingness and ability to help IT executives
build and operate infrastructures that embrace Linux and Open Source.
Furthermore, IT executives should work with their most trusted vendors
to ensure that architectures at those executives' enterprises become
and remain sufficiently flexible and elastic to support promising new
Open Source solutions as they appear. Such architectures will enable
IT executives to incorporate such solutions as they demonstrate the
ability to lower costs and deliver other business benefits, without
disrupting operations or enterprise elasticity.
Meanwhile, IT executives should ensure that their leading incumbent
management vendors have strategies and offerings adequate to address
growing enterprise Linux and Open Source support requirements - or begin
considering alternative solutions and vendors.
=====
Michael Dortch is a principal business analyst and the IT infrastructure
management practice leader at Robert Frances Group (RFG). RFG provides
business-centric, timely advice, consulting, and research about the
IT marketplace to Global 2000 IT executives, their teams, and their
senior executive colleagues. More information is available online at
http://www.rfgonline.com
Open-source databases took
a giant step toward the mainstream last month when Hewlett-Packard
began supporting MySQL and certifying it to run on HP servers--the
first major system vendor to do so.
HP's move adds to the growing evidence
that open-source databases--primarily MySQL--are becoming a viable
alternative to commercial databases from IBM, Microsoft, and Oracle.
"It's no longer the lunatic fringe," says Gartner analyst Kevin
Strange, who has seen a surge of interest in MySQL in the last six
to eight months.
Like the open-source Linux operating
system and Apache Web server, open-source databases are freely available
on the Web, and developers can download the source code and modify
it any way they like. But companies also can pay vendors such as
MySQL AB and PostgreSQL Inc. for support and other services in the
same way they can purchase Linux software and support from Red Hat
Inc.
Why the sudden interest in open-source
databases? Cost is the main reason. Companies are undertaking new
IT projects as the economy improves, but budgets remain tight and
IT managers are increasingly open to low-cost alternatives to high-priced
database software. MySQL implementation costs can be as little as
10% or 20% the cost of a commercial database, says Mike Gaydos,
the lead architect of MySQL solutions at IT services firm EDS.
Business-technology executives now
have a higher comfort level with open-source software overall. As
Linux, Apache, and the JBoss open-source application server gain
acceptance, IT execs who just a year ago might have balked at the
idea of using an open-source database are taking a second look.
Open-source databases lack some of
the sophisticated capabilities offered by commercial databases,
but they're widely perceived as capable of handling routine and
even critical computing tasks. AMR Research, in a report predicting
open-source databases will be widely adopted by 2006, found that
43% of companies using open-source databases say they can handle
mission-critical jobs today, while 37% expect them to be ready for
such tasks within 24 months.
Increased use of MySQL and
other open-source databases, in fact, represents something of a
revolt among IT buyers against IBM, Microsoft, and Oracle, which
continue to fill their database products with new features and technology
that many business users aren't ready to use. Oracle, for
example, built grid-computing capabilities into its 10g database,
even though many customers are a long way from building grid systems.
The government of Pottawattamie County,
Iowa, is swapping out its Windows server software for Linux. It
uses MySQL for several database applications and plans to convert
others to MySQL from Microsoft's SQL Server database. Aside from
the cost advantage MySQL has over SQL Server, county IT director
Thomas Broniecki claims MySQL is more secure than Microsoft's database.
Few companies, however, are ripping
out their Oracle, Microsoft, or IBM DB2 databases in favor of open-source
databases. Commercial databases are too entrenched within IT networks,
and businesses have too much invested in applications--either developed
in-house or packaged apps from PeopleSoft, SAP, and others--that
run on those databases.
Open-source databases move in most
often for new, custom-built applications, particularly within small
and midsize companies, educational institutions, and government
organizations.
"Pretty much every part of the business
runs on MySQL," says Corey Ostman, technology VP at PriceGrabber.com
LLC, an online comparison-shopping Web site. The database serves
up content to the Web site, which gets thousands of hits every second
during peak times, and tallies up the clickstream data used to calculate
fees paid by retailers to PriceGrabber.
PriceGrabber chose MySQL when the
company was started in 1999 because the database was easy to manage,
Ostman says. He had worked with Oracle's database and says it needed
constant attention and tuning. And while cost wasn't a major factor
in the initial decision to use MySQL, Ostman says it doesn't hurt
that its maintenance and support from MySQL AB costs "thousands
of dollars per year rather than [the] hundreds of thousands of dollars
per year" a commercial database would cost.
MySQL finds its way into some big
companies. Sabre Holding Corp. has 45 Linux-based servers running
MySQL databases to give travel agencies fare and seat-availability
information. But the heavy-duty job of calculating prices and processing
reservations remains on an HP NonStop transactional database.
While the roster of open-source databases
includes PostgreSQL and Berkeley DB, the momentum is rolling in
favor of MySQL, which has more than 4 million installations.
Doug's Impromptu Survey A customizable online survey using Perl,
Apache, and MySQL.
Some new scripts:
-
DTGraph Builds graphs/stats/alarms based on temperature data stored
in MySQL.
-
sync_db.pl A Perl script for syncing a MySQL database.
-
HandySQL A handy MySQL access module that is 20% faster than DBI.
-
mysqlwdb
Manages MySQL database tables via a Web interface.
-
CrudBoard
A fast mod_perl+MySQL web board with a clean interface
-
MySQL Backup
A Perl script to safely backup MySQL databases and tables.
-
DCVS A Perl/MySQL CVS server.
-
Solomon MySQL Web Interface
A Perl Web frontend to MySQL tables.
-
FutureForum Web Discussion Software Web based discussion forum written
in Perl and MySQL
-
MySQL Import Perl written script that imports data into MySQL with
a Web browser.
-
Pam-Mysql Pam-Mysql is a PAM module that allows authentication through
a Mysql database.
- *****
moodss Modular System, Database, and Network Monitoring Software
-
beancounter Automated stockmarket data gathering and evaluation.
-
BATTS A lean, elegant trouble ticket system with command line
and Web interfaces.
-
Bug Traction A Web-based bug tracking system.
-
Network And Service Monitoring System
A tool for managing and monitoring a network.
-
XAO Foundation Server
A hierarchical OO database for Perl
-
Cascade A Web-based content management system.
-
MID Allows for easy machine inventory and configuration management.
-
Flow Loader And Virtual Information Output A Cisco netflow collector
and graphing tool.
-
APLM An easy-to-use set of scripts for managing small libraries,
with a Web interface
-
my-swatch A log monitor based on Perl with MySQL and Psql features.
-
SupportTicket System A Web-based support email ticketing system
-
WWWdb Database-access over HTTP with consistency-check
-
bg perlyzer A collection of scripts that analyze Apache logs
in a Web environment.
-
FTP Index An FTP indexer and search engine.
-
food4thought food4thought is a freshmeat.net clone. Users who register
with the system can submit projects which are then verified by the administrator
of the system. Users can also submit articles and post comments on projects.
It is powered by a MySQL database.
-
mock market A Web-based stock brokerage simulation game.
-
Scoop A weblog management system
-
Spaminator A program allowing easy management of Sendmails.
-
Mysql-sdb A driver for using a MySQL database to store a zone in
BIND 9.
-
NcFTPd MySQL Authentication MySQL user authentication for NcFTPd.
-
MySQL-Bind8 A program to manage BIND 8.
-
oDns Php/Perl/MySQL-based frontend to the BIND 8.2 name server.
-
NovaDNS A user-friendly program to manage the BIND DNS server.
-
freshmeat.net Project details for Mysql Data Manager
-
Mysql Data Manager Perl-based Web-based MySQL client that provides
local and remote database access. It allows direct data access and management
via an easy and intuitive interface. Mysql Data Manager is a platform-independent
CGI script.
Brian Aker, OSDN
Track: Databases
Date: Friday, July 26
Time: 1:45pm - 2:30pm
Location: Grande Ballroom B
Do you find yourself in the position of having a database that has
a lot of data and a need to extract quantitative information from it?
Or do you find that in some cases selecting large amounts of data is
not practical. Large data sets can be costly too remove from a large
database. When you think of these types of needs you think of data warehousing.
From there you start thinking about big machines with large and costly
licensed commercial databases. MySQL offers "user defined functions"
which gives you the ability to extend MySQL for data warehousing needs.
Through these you can extend MySQL to do complex analysis inside of
the database. This is not limited to traditional character and number
data, exotic binary data like images and music can be analyzed as well.
Examples will show how you can take well known Open Source libraries
like Image-Magick and use these inside of MySQL.
Additional examples will be shown using myPerl. With myPerl you can
even bypass the need to learn the internal structure of MySQL UDF structure
and instead use a popular and easy to use scripting language like perl.
Download presentation file
Basically this creates a poor man's stored procedure
for MySQL using perl. You can store perl in a column (or just pass it
directly to the myperl function). A sample call would look like: mysql>
select myperl("$MYSQL_MYPERL_VALUE = 2*3");
New for this version:
You can now specify an integer for the first parameter if you need something
longer then 255 characters.
Perl Interpreter is not longer created for each connection!
Other random cleanup
PL/Perl allows you to write PostgreSQL functions and procedures in
Perl. To include PL/Perl in the build use './configure --with-perl'.
To build from this directory use 'gmake all; gmake install'. libperl
must have been built as a shared library, which is usually not the
case in standard installations.
Consult the PostgreSQL User's Guide and the INSTALL file in the
top-level directory of the source distribution for more information.
25.2. Building
and Installing PL/Perl
If the
--with-perl
option was supplied to the
configure
script, the PostgreSQL build process
will attempt to build the PL/Perl shared library and install it in the
PostgreSQL library directory.
On most platforms, since PL/Perl is a
shared library, the
libperl library must
be a shared library also. At the time of this writing, this is almost
never the case in prebuilt Perl packages. If this difficulty arises
in your situation, a message like this will appear during the build
to point out this fact:
*** Cannot build PL/Perl because libperl is not a shared library.
*** You might have to rebuild your Perl installation. Refer to
*** the documentation for details.
If you see this, you will have to re-build
and install Perl manually to be able
to build PL/Perl. During the configuration process for
Perl, request a shared library.
After having reinstalled Perl,
change to the directory
src/pl/plperl in the
PostgreSQL source tree and issue the
commands
gmake clean
gmake all
gmake install
to complete the build and installation
of the PL/Perl shared library.
To install PL/Perl and/or PL/PerlU
in a particular database, use the
createlang script,
for example createlang
plperl dbname
or createlang plperlu
dbname.
Tip:
If a language is installed
into template1,
all subsequently created databases will have the language installed
automatically.
Have you ever wanted to search text stored
in your database, but couldn't figure out how to do it efficiently?
Are you lazy like me and don't enjoy maintaining reverse indexes, dictionaries,
and word scores? You're in luck. The release of MySQL 4.0 has made searching
text stored in databases available to the masses.
MySQL has had
FULLTEXT searching in one form or another since version 3.23.23.
FULLTEXT
indices in MySQL allow database administrators and programmers to designate
any character-based field (CHAR,
VARCHAR,
or TEXT)
as a FULLTEXT
index, which allows for complex text searching against data stored in
those fields.
This feature is not to be confused
with the LIKE
function in MySQL. LIKE
works more along the lines of a regular expression. On the other hand,
FULLTEXT
indices are fully indexed fields which support stopwords, boolean searches,
and relevancy ratings.
This article assumes you have a
working installation of MySQL, a good understanding of how MySQL works,
and a basic understanding of web programming (with PHP, Perl, or something
similar). Further, this article may not be of any interest to those
who are already using FULLTEXT
indices in a production environment.
How it Works
The MySQL team has made it extremely
easy to add FULLTEXT
searching to your tables. They are created much like regular
KEY and
PRIMARY KEY
indices. For the purpose of this article we are going to make a basic
blog table, put some data into it, and start searching. Before we get
ahead of ourselves we need to create some tables.
Creating the tables
-- The main blog table with our FULLTEXT index
-- Nothing extreme here, but you get the idea
CREATE TABLE blog_entries
(
entryID INT(9) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
posted INT(11) UNSIGNED NOT NULL DEFAULT '0',
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0',
title CHAR(255) NOT NULL DEFAULT '',
entry TEXT NOT NULL DEFAULT '',
PRIMARY KEY (entryID),
KEY (posted),
KEY (categoryID),
FULLTEXT(title,entry)
);
-- A category table so you can organize your posts and
-- later do some fun searching based on such data.
CREATE TABLE blog_categories
(
categoryID TINYINT(2) UNSIGNED NOT NULL DEFAULT '0' AUTO_INCREMENT,
name CHAR(75) NOT NULL DEFAULT '',
PRIMARY KEY (categoryID)
);
-- Add some data into your blog
INSERT INTO blog_categories VALUES (1,'Personal');
INSERT INTO blog_categories VALUES (2,'Work');
INSERT INTO blog_categories VALUES (3,'Editorials');
INSERT INTO blog_entries
VALUES (1,
1050942000,
1,
'About miester',
'I was born in michigan in 1980 in a small town called Adrian.
My mother is named Sue, while my father is named Mike.
They currently live in a small town called East Jordan. On April
27th, 2003 I will graduate from Eastern Michigan University with a
degree in Computer Information Systems.');
INSERT INTO blog_entries
VALUES (2,
1050942000,
2,
'Today at work',
'While I was at work today I was having some problems
with the RAID array. It seems that we have a rogue cron script that
is causing problems. When I find out more info I will post it here.');
INSERT INTO blog_entries
VALUES (3,
1050942000,
1,
'Vacation!',
'After I graduate I am taking a 2 week vacation. On my
agenda is a trip to Washington DC to see my girlfriend\'s sister
as well as throwing a few discs at the local disc golf course.');
INSERT INTO blog_entries
VALUES (4,
1050942000,
1,
'Vacation!',
'I have had a horrible cold for the last few days. Today I drank a
revive vitamin water with 150% of my daily dose of vitamin C. That
should help things out.');
Querying the Data
Now that we have data in our tables
we can begin to query it. There are some restrictions to
FULLTEXT searching,
which are covered below. You will want to read over the restrictions
before you use FULLTEXT
indices in a production environment. For now we are going to do a simple
query for the word mother.
mysql> SELECT entryID,title
-> FROM blog_entries
-> WHERE MATCH (title,entry) AGAINST('mother');
+---------+---------------+
| entryID | title |
+---------+---------------+
| 1 | About miester |
+---------+---------------+
1 row in set (0.00 sec)
There are a few things to note
when querying FULLTEXT
indices. First, MySQL automatically orders the results by their relevancy
rating. Second, queries that are longer than 20 characters will not
be sorted. Third, and most importantly, the fields in the
MATCH() should
be identical to the fields listed in the table's
FULLTEXT definition.
All other MySQL syntax works as
you'd expect with a FULLTEXT
search, meaning you can further limit your search terms. We could search
blog entries based on posting date or category. If you let your imagination
wander you can think of all sorts of ways to filter your data. Let's
look for blog entries that only appear in the
Personal category
and match the term michigan.
mysql> SELECT E.entryID, E.title, C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title, E.entry) AGAINST ('michigan') AND
-> E.categoryID=1;
+---------+---------------+----------+
| entryID | title | name |
+---------+---------------+----------+
| 1 | About miester | Personal |
+---------+---------------+----------+
1 row in set (0.00 sec)
Note that we not only did a join
but also filtered the results based on the category. Another thing to
note is that FULLLTEXT
indices are not case sensitive. If you would like to use MySQL's relevancy
rating in your code you can add the
MATCH() ... AGAINST() clause to your
SELECT
statement as well.
mysql> SELECT E.entryID, E.title, C.name,
-> MATCH (E.title, E.entry) AGAINST ('michigan') AS score
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title, E.entry) AGAINST ('michigan') AND
-> E.categoryID=1;
+---------+---------------+----------+-----------------+
| entryID | title | name | score |
+---------+---------------+----------+-----------------+
| 1 | About miester | Personal | 1.2635315656662 |
+---------+---------------+----------+-----------------+
1 row in set (0.00 sec)
Boolean Searches
Probably the most anticipated feature
in MySQL 4.0's FULLTEXT
is the ability to do boolean searches without having to process the
query strings. This means you can add
+s and
-s to your
queries, along with a host of other commands, and MySQL will interpret
them for you.
mysql> SELECT E.entryID,E.title,C.name
-> FROM blog_entries AS E, blog_categories AS C
-> WHERE E.categoryID=C.categoryID AND
-> MATCH (E.title,E.entry) AGAINST ('+vacation -washington' IN BOOLEAN MODE) AND
-> E.categoryID=1;
+---------+-----------+----------+
| entryID | title | name |
+---------+-----------+----------+
| 4 | Vacation! | Personal |
+---------+-----------+----------+
1 row in set (0.00 sec)
We have two entries with the word
vacation
in the title, but since we removed washington,
entryID 4 does not show up in the result. You can read all about
IN BOOLEAN MODE
on MySQL's FULLTEXT
manual page.
Restrictions
A few restrictions affect MySQL
FULLTEXT
indices. Some of the default behaviors of these restrictions can be
changed in your my.cnf or using the
SET command.
FULLTEXT
indices are NOT supported in InnoDB tables. - MySQL requires that you have at
least three rows of data in your result set before it will return
any results.
- By default, if a search term appears
in more than 50% of the rows then MySQL will not return any results.
- By default, your search query must
be at least four characters long and may not exceed 254 characters.
- MySQL has a default
stopwords
file that has a list of common words (i.e.,
the,
that,
has)
which are not returned in your search. In other words, searching
for the
will return zero rows.
- According to MySQL's manual,
the argument to
AGAINST()
must be a constant string. In other words, you cannot search for
values returned within the query.
Altering
FULLTEXT's Default
Behavior
There are several ways to alter
the default behavior of FULLTEXT.
MySQL has some
tips for fine tuning the FULLTEXT
search, but the details are a little sparse. The most common problem
is the four character minimum word length on queries. Before we go over
that, let's review the variables associated with the
FULLTEXT searching.
mysql> SHOW VARIABLES LIKE 'ft%';
+--------------------------+----------------+
| Variable_name | Value |
+--------------------------+----------------+
| ft_boolean_syntax | + -><()~*:""&| |
| ft_min_word_len | 2 |
| ft_max_word_len | 254 |
| ft_max_word_len_for_sort | 20 |
| ft_stopword_file | (built-in) |
+--------------------------+----------------+
5 rows in set (0.00 sec)
The variable we wish to change
is ft_min_word_len.
According to the manual we should be able to change this via the
SET VARIABLE
command, but, in reality, this does not work. After asking the mailing
list about this problem I was told this had to be specified as a startup
option. To change the minimum query string to three characters, start
the MySQL server as follows.
$ cd /path/to/mysql
$ ./bin/safe_mysqld --user=mysql -O ft_min_word_len=3 &
After you have restarted your MySQL
server (don't forget to change your startup scripts) you have to rebuild
the FULLTEXT
indices. The manual suggests the following command:
-- Replace tbl_name with the name of your table
mysql> REPAIR TABLE tbl_name QUICK;
After you have rebuilt your indices,
you should be able to search with query strings of three or more characters
instead of the default four character limit.
Having fun with MySQL
FULLTEXT Searching
I'll save the implementation details
for a later article, but here are some interesting ways in which you
could use MySQL FULLTEXT
searching to finding data on your website more interesting.
PHP
A great way to add cross referencing
to articles would be to store a query (i.e.,
linux for a post
about Debian GNU/Linux) with each article. If an article had a query,
PHP could then perform a FULLTEXT
search, returning those results as "Related Articles". Furthermore,
you could use PHP to create an advanced search script that allowed users
to search the database based on category, criteria, pricing, etc.
ispell
ispell
is a great tool for open source developers to use to make their applications
more user friendly. By adding ispell
to your search engine, you can check the spelling of each query, offering
alternate queries if the query was spelled wrong. Everyone's favorite
search engine does something similar.
Looking towards the Future
According to the manual, the MySQL
team
has a lot they still want to implement into
FULLTEXT searching.
Here is a brief overview of those enhancements:
- Proximity operators
- Making the stopword lists depend
on the language of the data
- Stemming
The proximity operators will really
make FULLTEXT
searching impressive. This will allow you to do searches on words based
on how close together they are. For example, if you currently searched
for 'mysql search' you would get results even where
mysql and
search appear
at opposite ends of the document. With proximity operators, the scoring
algorithm gauges how close together the words are. Documents where
mysql
and search
appeared directly next to one another would score higher than documents
where they were not close together.
Stemming
is a great way to make search engines smarter. This would allow MySQL
to search for words that share the same lexical root. For example, queries
for running
would return documents with ran
and run
as well as running.
At a partnership announcement with Dell Computer in New York City
back in April, Oracle (Quote,
Company Info) CEO Larry Ellison stood alongside his fellow CEO Michael
Dell and discussed how the Linux open source operating system was stealing
market share from Microsoft's Windows products.
In the future, he asserted, Microsoft would be all but irrelevant
by the Linux trend. The crowd nodded and smiled at his screed. This,
you could almost hear them thinking, was Larry at his best.
But then someone in the audience took the flipside to his thesis,
asking Ellison: If Linux, in all of its open-source glory, would irreparably
harm Microsoft's proprietary approach, could he not see the threat of
open-source database vendors to his company? Ellison said no,
and then enumerated Oracle's market strength, brand, track record and
high level of security as reasons for Oracle's staying power.
Few can dispute those points. Oracle, which specializes in selling
to high-end customers, is still a $10 billion company at the top of
the database market. In the most recent
calculations from research firm Gartner, Oracle retained a whopping
43 percent of the overall relational database market. IBM and Microsoft,
which also cater to small and mid-sized businesses with their database
products, are the other two most successful vendors in the vaunted space,
with shares of 24 and 17 percent, respectively. It should be noted that
IBM, which bumps heads with Oracle often in the quest for large customer
contracts, has posted impressive gains in the database sector, and led
during the last quarter in terms of new, licensed users.
But while commercial database vendors remain locked in a lunge and
parry fight for customers, in the background is a small movement in
which providers are open-sourcing their database as a less expensive
alternative to the ingrained vendors. This open-source movement, which
some experts predict will follow a similar arc to Linux, is led by a
Swedish firm that has been quietly building momentum as an alternative
in the market for small business, departmental or commodity use: MySQL.
Industry opinion is divided as to whether MySQL or another of its
ilk might challenge Oracle, Microsoft or IBM at the low end of the database
market, where fewer specialty features are needed.
IDC analyst Carl Olofson summed up open-source databases technology:
"Open source relational database management system (RDBMS) software
is a disruptive technology. As such, it is used, not to address the
high end requirements of those who demand the latest and greatest, but
to address the needs of application and tools developers and vendors
who are looking for just enough database technology to provide an affordable
solution."
This is certainly comparable to Linux, which has enjoyed growth propelled
by many major vendors, such as IBM and Oracle, as well as myriad Linux
providers like Red Hat and SCO. These firms have pumped many dollars
and resources into the movement to provide an alternative to proprietary
systems such as Unix and Microsoft's Windows operating system. Isn't
it imaginable, therefore, that open-source databases might follow the
same path? After all, Linux started as a small movement, too.
MySQL's value proposition (contrary to Oracle, Microsoft or IBM)
is to provide the code for its MySQL database for free, under the free
software/open source
GNU General
Public License (GPL) or a non-GPL commercial license, which they
make money from through service fees. And it's working.
In the spirit of "If you build it, they will come," MySQL has been
building and users have been coming: MySQL has seen an estimated 4 million
installations and over 30,000 downloads per day for its flagship product.
The company counts Yahoo!, Lucent Technologies, Sony Pictures Digital
Entertainment among its customers. At a time when investor dollars are
scant, the firm this month landed $19.5 million in funding, led by Benchmark
Capital, and appointed one Benchmark general partner Kevin Harvey to
its board.
MySQL versus the big three
Impressive credentials and support? Perhaps. But why go to an open-source
database when Oracle 9i, Microsoft SQL Server and IBM DB2 are held in
such high regard? In short, for the same reason why businesses are moving
to Linux platforms: to save on total-cost-of-ownership, among other
areas. The importance of this cannot be understated in this weak economy.
Gone are the days when CEOs were looking to pad the top line; here are
the days where the CEOS scrutinize how much cash they can shave off
the bottom line.
So, consider a few options from Oracle, Microsoft and IBM. Oracle
offers two main packages, an enterprise edition geared for high-end
use at $40,000 per processor and a standard edition for $15,000 per
processor, which is targeted for small- and medium-sized businesses
(SMBs). Similarly, Microsoft has a standard edition of SQL server for
$4,999 per processor, and an enterprise-class offering for $19,999 per
processor. IBM's DB2 portfolio features an enterprise edition for $25,000
per processors, and a workgroup edition for $7,500 per processor (comparable
to Oracle and Microsoft's standard editions).
By contrast, MySQL offers its database on a much lower scale and
cost to the casual user. MySQL CEO Marten Mickos discussed his company's
value proposition and licensing model in a recent interview.
"We are what we call a 'second wave open source' company, meaning
we have a functioning business model that is in harmony with our free
software principles," Mickos said. "Our dual licensing allows us to
offer our software free of charge under the GNU General Public License
(GPL) while at the same time selling the same product under a regular
commercial license. We can do this because we own our software and have
the freedom to license it any way we wish."
Olofson explained where open-source database technologies, such as
those provided by MySQL, and two of its brethren,
PostgreSQL and
Firebird,
fit into the market.
"Although the huge majority of MySQL usage today derives from free
open source downloads, MySQL hopes to move from the geek community to
the business community through the embedded database model, and they
are counting on this channel as a key to future revenue growth," Olofson
said.
Mickos, who said the company's goal is to "make superior database
technology available and affordable to all," said commercial licenses
account for nearly two-thirds of its revenues. The remainder of the
company's earnings come from support and services. MySQL charges a flat
fee of $440 per server and customers can have as many users and as many
CPUs as they want.
"Put this in context of the pricing of other DBMSs [database management
systems] and you can understand why we sell so much," Mickos said.
Now, given the price comparisons between MySQL and the other, strictly
commercial vendors, why isn't MySQL grabbing up market share by the
fistful? Does MySQL not threaten the three giants? No, and Mickos and
his firm don't claim to. There are many, many reasons for this, some
of which Ellison alluded to last April. MySQL is new, much smaller than
the database behemoths, and does not offer much of the special functionality
of large database vendors, something that they say the high-end, more
specialized customers require.
"We focus on the commoditized part of the market - the one in which
performance, reliability, convenience and price are the determining
factors," Mickos said. As such, Mickos said he and his outfit feel they
are complementary to say, Oracle, or IBM, who offer some highly specialized
features, as opposed to competitive. In practical terms, while Oracle
may power the high-performance needs of a search firm, certain departments
might use MySQL as a simple alternative.
What the commercial vendors say
It's tough to find members of the commercial side of things that
would argue with Mickos. Jeff Jones, director of strategy data management
at IBM, praised MySQL and its brethren, noted that they are commanded
by smart people with some great ideas. But he still sees the pros of
the commercial side.
"I think you can argue there is a focus and a clarity of requirement
to the commercial side," Jones said. "IBM has been working with databases
since the '70s and things are done differently. There is a different
degree of ability, of relational theory, for what we do. We have to
bring scalability optimization, we have to understand how to extend
relational theory to handle things like XML, to extend search technology,
integrate more analytic capability, federate integrated business intelligence."
Interestingly, IBM has a small database offering that can slide into
the low-end slot alongside MySQL, in the recently released DB2 Express.
Geared for the low-end market, or a handful of seats, DB2 Express is
priced at $499 per server, or $99 per user.
Ken Jacobs, vice president of product strategy at Oracle, shares
Jones' feelings about the main differences between the open-source database
players and commercial vendors of today. But, like Ellison, he suggested
that it is Microsoft that has the most to lose from open-source, arguing
that the evolution of open-source databases like MySQL could chomp away
at Microsoft's low-end market share.
"Microsoft may be the real target or victim here," he said. "When
people make decisions to purchase a database one of the deciding factors
to go with open-source is that it is cheap." Admitting that Oracle and
IBM's products are not as cheap because they target customers with more
specialized needs, Jacobs said any cannibalization from low-end products
is likely to be in Microsoft's camp. Jacobs said Oracle does not feel
threatened by MySQL, and, like Mickos, said he sees it as a complementary
product. "These DBs are interesting and can serve a niche. In fact,
I'd argue that MySQL customers are also Oracle customers who have outgrown
SQL Server."
Sheryl Tullis, product manager for SQL server at Microsoft, begs
to differ. First, she disputed the notion that that SQL server's success
is tied to SMBs, as many people suggest. "In the recent Gartner figures,
SQL server grew 16.8 percent, Oracle declined 20.5. That shows customers
are seeing value in SQL Server."
"We think [open source databases] are a different segment than who
we sell to," Tullis said. We see MySQL as being primarily for hobbyist
technologies, or departmental use. We see it as something a developer
might use if they do not want to go through IT procurement."
Tullis said if Microsoft's customers do need a lightweight database,
Microsoft offers MSDE, or Microsoft SQL Desktop Edition, and embeddable
engine developers or ISVs can use as a datastore. And it's free. Moreover,
she argued that Microsoft has actually learned, through MySQL, how to
create a "database community that is interesting and passionate."
One of the knocks on open-source databases is they don't have some
of the features large-scale servers do, and are therefore not attractive
to customers. Meta Group analyst Charlie Garry has heard the arguments
about MySQL and its brethren not having the special functionality or
scalability of a DB2 or a 9i. He has a counter argument.
"There is a line of thinking that Oracle overshot the market by having
too much feature functionality," Garry said. "One is XML databases.
There is not a whole lot of people who are storing data in XML format."
"Now, there's an interesting feature I would guarantee, most people
have never even heard of it or knew it was in 9i," Garry said. Then
there are things like a partitioning option. More than 60 percent of
enterprises are running on 4 processors or less and 50 percent of the
resources are unused. A lot of people don't need partitioning, or 64-bit
indexing that are offered by commercial vendors, so these are not necessarily
things that people want to pay for."
"The beauty of open-source databases is that that we often see a
cycle among commercial vendors, where they to continue to add feature
functions to trump that other guy," Garry said. "Open-source databases
are forcing users to think what is important to me, and think about
right-sizing. When you're a tech leader like Oracle, you run into this
rut where you have to drive revenues, listen to investors who tell you
need these esoteric features."
Pathwalker
(103)
* <hotgrits@yourpants.net>
on Saturday May 24, @03:33PM (#6031858)
(http://house.ofdoom.com/~hungerf3
| Last Journal:
Friday May 23, @05:20AM) |
Personally, I like using "cd
/usr/ports/databases/postgresql7 && make install"
rather than using a binary package, as that way you have the
source code on your system, and you can use all of the wonderful
things in the contrib directory.
tsearch is a very nice GIST indexed full text search
that does word stemming.
reindexdb is a handy way to regenerate all of the indices
in a database without interrupting anything.
earthdistance is far faster than writing the same thing
as a SQL function.
ltree is wonderful when you have to deal with a hierarchy.
And those are just the ones I remember offhand... |
Chapter_8 Data Mining and Statistical
Analysis Using SQL A Practical
Guide for DBAs
PostgreSQL101
-- quick start for Linux/FreeBSD users
An interesting interview with Michael "Monty" Widenius is the designer
and lead programmer for the MySQL database. Some little known facts...
I know that MySQL really isn't
made to work specifically with PHP alone, but since they're so popular
together do you think there might be some type of collaboration in the
future? And if so, can you give us any details? If that question doesn't
make any sense, do you like beans?
Yes, we have are very interested in a
close collaboration with the PHP people. In fact, we already work closely
with them to ensure that the MySQL interface that is included with the
latest PHP versions are kept up to date. We are also adding new functionality
to MySQL that will help PHP users:
- Make it possible to read MySQL connection
options (like user and passwords) from MySQL option files from within
PHP.
- Add prepared statements in MYSQL
that will work nicely with PHP.
We are adding stored procedures (with
the ANSI SQL 99 syntax) to MySQL. When this is done we will add support
for PHP inside MySQL (as an alternative stored procedure language).
I can eat beans, but prefer to not eat
them every day...
isolti's question:
Hi Monty, I like your product. It works great. My question is what
feature(s), if any, is missing from MySQL that is in the proprietary
Oracle? What missing feature (if any) could stop somebody from migrating
to MySQL from Oracle?
This depends on lot of how your application
is written. My belief is that MySQL's current functionally is good enough
to make it possible to move a substantial portion of all new database
applications to MySQL without any major difficulties.
The major difficulties come with old
legacy applications that use either a lot of Oracle specific features
or some of the ANSI SQL features that MySQL is still missing.
The biggest stumbling blocks are sub
selects and stored procedures. Some applications (but much fewer) are
also very dependent on views and foreign keys.
In the MySQL 4.1 source tree we already
have working sub selects and we have three guys working on adding stored
procedures to MySQL (for MySQL 5.0). Full foreign keys support is also
planned for 5.0. In MySQL 3.23 and 4.0 we have only support for foreign
keys for InnoDB tables.
When the above accomplishments are met,
it will be even easier to migrate from traditional databases, like Oracle,
to MySQL.
If you are depending on clustering (where
replication is not good enough) or some of the more exotic capabilities
of a database like Oracle, then it will still take some time before
you can easily migrate to MySQL. Fortunately this is not true for most
applications.
In practice we hear every day of people
who are migrating from Oracle and other SQL databases to MySQL with
good results and we are working hard on our side to ensure that this
trend will continue.
webhappy's question:
Considering your long experience in designing databases, what do
you consider the most challenging aspect? What parts were the most fun?
In addition, because database software usually involves squeezing the
best performance, what aspects of computer science do you feel are the
most important in developing a next-generation database suite? And finally,
do you think designing a database (of course, not something as full-featured
as mySQL) could be a fruitful computer science project? :)
The most challenging aspect is to get
the code bug-free for a lot of users that use the database in ways that
you could not have even imagine when you wrote the code. Apart from
that, to get the SQL optimizer to do right in 'most' cases is a challenging
task that one could work on for several lifetimes:)
Most fun is the feedback you get from
enthusiastic MySQL users who are successfully using your product in
different ways and are impressed with how well it works.
The "science" that I find most important
in developing databases is to understand how to get things to work in
really small systems and design algorithms that are scalable from these
to large systems. Unfortunately this is not something that is well known
and only people who have had first hand experience with programming
on 16K machines (or less) know this by heart.
Yes, designing a database can be a very
fruitful computer science project; What can be equally fruitful is to
take one of the open source databases (like MySQL :) and extend it to
do things that it could not do before. This way you have not only done
something that is good for yourself but also something that other people
can enjoy and benefit from.
Jesse's question:
You have an interesting business model. Most companies that give
away a product haven't been doing so well. I am interested to know where
more of your revenue stream comes from, pay for support or paid (non-GPL)
licenses? On the same note, what tips would you have for someone pursuing
a similar business model?
For the moment the biggest part of our
income comes from license sales of the non-GPL licenses, but support
and training also provides good income to MySQL AB.
We have found that the GPL license works
extremely well if you have a library that you can get very widely used
and that is extremely useful to embed in a lot of commercial applications.
A database server (MySQL), a language engine (Zend) or a GUI widget
kit (QT) are good examples of this. It's not that good for standalone
applications that people don't have a reason to embed in their products.
The tip I have is if you create an application
and release it as GPL with the intention of selling commercial licenses
for a non-GPL version, you should start giving a lot of free support
to everyone that uses it and actively help people use and spread it.
I believe that you should NOT later change
your license to a non-open source/free software license or have two
different versions of the product --one (with less functions) for GPL
users and a more featured version for paying customers. It's always
better to be fully open source/free software compatible than try to
create a hybrid.
imerilai's question:
Do you have plans for seriously developing a GUI for MySQL administration
and SQL development? Something like MyCC, but much more functional?
Or is your strategy focused on developing the core server and leaving
GUI development for third parties?
Our current strategy is to work full
time on MyCC and have this as a framework for all future GUI applications.
The idea with MyCC is that anyone should
be able to create dynamically loadable 'plugins' to enhance it the way
you want.
For example, if you don't like the way
the current ALTER TABLE works in MyCC you should be able to code an
alternative (better?) interface for ALTER TABLE and easily replace the
default functionality with this.
By having a lot of plugins -- some created
by us and others created by third parties -- we hope to have all the
functionality in the GUI that you could ever ask for.
dimonemon's question:
My question is development related. Are there any, or will there
be some books, tutorials, etc. that explain internal structure of MySQL
so that curious people like myself can analyze and study the code and
understand how subsystems interrelate and work together. Thanks, Dmitriy
This is something that we would really
like to see happening and we have been working on this ourselves (albeit
slowly because of time constraints).
In the MySQL source distribution the
file Docs/MySQL-internals.texi includes a lot of comments of the MySQL
source. Whenever we feel that something within MySQL needs to be documented
so that we can understand how it works, we add it to this document.
Over time we hope that the above document
will grow to cover all aspects of MySQL, but for the moment the source
code is the best way to learn the internals of MySQL.
Grok's question:
As any Slashdot poster would be happy to inform you, the four big
features missing from MySQL are Stored Procedures, Views, Triggers and
Foreign Keys. I happen to disagree with them and think the most needed
new feature is better database documentation. PHPMyAdmin has special
tables that allow you to do this, but it needs to be more standardized
which means the tables need to ship by default. Specifically, Foreign
Key documentation and descriptions of each field in a table. This would
allow pretty charts of the DB structure, but more importantly would
allow those who work with the table to understand more easily how it
works. Those above five features aside, what do you think will/would
be the most important change to MySQL?
MySQL 4.1 will allow you to store foreign
keys definitions and retrieve them for all table types. (Currently we
only support this for InnoDB tables). MySQL 4.1 already supports comments
for each field, in addition to table comments that 3.23 supports.
Apart from the above features the most
important aspect is to add all the other missing ANSI SQL 99 features
without making MySQL slower.
We are working on OLAP functionality
in 4.1 (CUBE and ROLLUP) and are also constantly researching new ways
to make MySQL even faster for data warehousing uses.
Another important feature is the ability
to emulate other database syntax to make it easy to emigrate to/from
them to MySQL.
lindset's question:
What do you think MySQL's biggest advantage(s) over other Database
servers are? And what do you think is the reason for the success of
MySQL on the web (MySQL on the backends of sites)?
Speed, reliability and light footprint
are the biggest advantages of MySQL.
From the start MySQL was optimized for
web applications and when the web took off around 1998 and people started
to look around for a database, MySQL was there ready to be used.
In other words, the main reason was being
in the right place at the right time with a reliable product that everyone
suddenly needed.
Matt's question:
What was your inspiration behind MySQL? Why did you begin development
in the first place?
First, MySQL was developed as a SQL interface
on an old database motor that I had been working with (and written)
over a period of 15 years.
We needed something that would enable
web users to get access to the data and David Axmark (a co-founder of
MySQL AB) and I thought that SQL would be the best language for this.
In other words, we developed MySQL because
we needed it ourselves. We released it as open source because we believed
that we had created something good and thought that someone else could
probably have some use for it. We became inspired and continued to work
on this because of the very good feedback we got from people that tried
MySQL and loved it.
andyl's question:
We are currently moving from Access to MySQL, but the one thing that
is sorely missed is the reporting features of Access. I know that I
can use ODBC to connect and still use Access, but I would really like
to get rid of it completely and find a lower cost (and better!) solution.
Are there any plans for MySQL to offer something along these lines,
or do you know of anything that can do the job?
Over time, the MySQL control center (MyCC)
will include most of the functionality in Access that most users need.
We have a long way to go for this but as more and more developers use
MyCC and add new plugins to this we hope that we we'll have something
for Access users in the not too distant future.
You can find the MySQL control
center on the download pages at
http://www.mysql.com
is a simple Perl script to sync a MySQL database from a dumpfile.
For each row in the database that is different from that in the dumpfile,
the differences are output and the user is asked what action should
be performed. If there is a modification timestamp in the table, a suggestion
is made to the user.
db2ssd ist a (no longer simple) perl-program that connects to
a database, extracts it's structure and produces a diagram readable
by the following programs
-
TCM's
"Static Structure Diagram" (UML) editor
- Gnome
DIA
It is somewhat inspired by
PostgreSQL
AutoDoc but aims at beeing database independent (currently MySQL,
Postgres and ODBC are supported at different levels of completeness)
Since db2ssd now supports DIA as well, it will be renamed to
REVENGE soon (REVerse ENGineering -> REVENGE, see?). I still
don't know what the last "e" might stand for :)) The internal modules
are already contained in a package named Revenge.
Build XML-based applications with open source Xindice, a native XML
database system, and see how this technology differs from relational
database management systems. (Web
architecture zone)
Comment: Web sites insecure as ever.
By David Rae
[22-05-2002]
Most, if not all, of corporate web sites
are fundamentally insecure. And this insecurity can allow attackers
to access databases, delete or change information, and cause absolute
chaos with very little effort or technical know how.
The problem is with web applications.
Back in the good old days when companies used the internet for nothing
more than hosting an elaborate electronic brochure, there was no
threat. The IT guys would have little to do with the process, with
the marketing department taking responsibility for outsourcing most
of the work to third-party web developers.
Well things have changed. The level
of interaction through corporate sites is overwhelming, and web
applications allow this interaction to take place - whether it be
shopping carts, authentication services or money transfers.
Whatever interaction takes place,
information on the database has to be accessed. The commands coming
from the web site are seen by firewalls and intrusion detection
systems as bona fide - SQL commands are seen as legitimate.
Unfortunately, attackers can use
this level of trust to their advantage by sending dubious requests
to a database. The firewall and IDS will see the request as SQL,
and let it through, but it is extremely unlikely the database will
be instructed to look for 'expected' requests.
A direct SQL injection is the process
of making a direct call to the database. It is frighteningly simple
to carry out, and can have horrendous results for your business.
On many websites a simple SQL command
will enable attackers to change such delicate information as Admin
passwords and the price of goods listed in an online store. The
scope for damage is limited only by the imagination of the attacker,
and the strength and security of your web applications.
So what can you do about it? Short
of ripping down your entire web site, and starting from scratch,
securing every single web application is nigh-on impossible.
But an excellent place to start is
the Open Web Application Security Project (OWASP). This was started
in September 2001 by developers and security professionals to address
this very problem.
Download the 'Guide to Building Secure
Web Applications and Web Services' (the latest draft was released
10 days ago) and try very hard not to cry into your beer. It makes
fascinating, if terrifying reading.
Being aware of the problem is the
first step. It allows you to go to the board, set out your stall
and say "This is what the problem is, this is why the problem occurred,
this is what could happen and this is what needs to be done".
It also allows you to question web
developers and any third-party security consultants you choose to
hire, with confidence and knowledge of the problem.
OWASP is also working on an Open
Source tool called Web Scarab that will test the security of your
web applications. The group hopes to have the software completed
within six months and it will be able to test a complete web site,
or individual applications for security vulnerabilities.
Comment on this story
www.owasp.org
www.kavado.com
WODA is a standalone database management
system for the World Wide Web. It consists of a Perl program e.g. woda-uk.pm,
which contains WODA engine. Each individual database application
is defined in the database definition file (definition). This
file defines the data dictionary, customizes the user interface and
then calls the engine for all the processing. It uses two hashes to
do so:
- Hash WBB defines features
of a whole table, e.g. $WBB{managerEmail}="joe@music.com"
defines email address of the manager of the database.
- Hash WBF defines features
of table fields, e.g. $WBF{name,help}="Enter
your first name" defines help text
for the field 'name'.
One engine therefore supports several
databases and is entirely database independent. All operating system
dependent parameters are defined within the engine. There are a few
language specific engines such as woda-uk.pl or woda-si.pl for the English
(uk) and the Slovenian language (si). Friends on the Internet also created
translations into other languages. So can you.
Freshmeat
XML-DBMS (Perl)
Perl module for transferring between
XML documents and relational databases.
Development Status :: 5 - Production/Stable
[filter]
Environment :: Other Environment
[filter]
Intended Audience :: Developers
[filter]
License :: Public Domain
[filter]
Operating System :: OS Independent
[filter]
Programming Language :: Perl
[filter]
Topic :: Database :: Database Engines/Servers
[filter],
Database :: Front-Ends
[filter]
The best-known databases these days are
based on SQL, but are often overkill for what you need to do. This review
discusses lighterweight alternatives, including xBase, DBM, and ISAM
systems.
Remember those heady days of the mid-to-late
1990s? When Webmonkey was required daily reading, Hotdog and HoTMetaL
Pro were the "cool" HTML editors (though vi and Notepad predominated),
and the Browser Wars were relevant? When virtually all Web sites were
collections of static pages, even if generated via some dynamic process(es)
such as VB widgets or Perl scripts? When Perl CGIs and .shtml pages
were the only true approximation of dynamic Web development available?
When Java still meant coffee, ASP was a snake, and Linus was a character
in the Peanuts cartoon? Remember? Ah, the bad old days.
Today, things on the Web are unbelievably
different. Reflecting the entire culture of the Internet, the Web is
today a collection of mostly dynamic Web pages. From the high-traffic
sites running on custom or packaged software (such as Vignette Story
Server or Future Tense), through the smaller business sites running
off Cold Fusion/Access, to individual sites running off
Blogger or similar packages/services, dynamic
pages are overtaking static pages at an increasingly rapid rate.
MySQL and Perl have been around for quite
a while. They are still widely used even if the "fashion" is changing.
This article talks about these two products working together as a whole,
either on the Internet or on your local network. The provided example
is written for Unix systems, free or not, even if it can be adapted
to other widespread "systems". What this article is : a small review
of what can be done with this pair, showing ease of use, speed, reliability,
security... What this article is not : neither a MySQL tutorial nor
a Perl tutorial; neither a MySQL review nor a Perl review. Accordingly,
we'll see MySQL at work in combination with Perl, without forgetting
that "there is more than one way to do it".
[Aug 23, 2001] LinuxPR: Great Bridge releases integrated open source
platform
"Great Bridge LLC, the leading provider
of solutions based on the PostgreSQL open source database, today announced
the release of Great Bridge WebSuite, an integrated open source platform
for building high-performance Web-based applications. Great Bridge WebSuite
integrates the enterprise-class Great Bridge PostgreSQL database with
the popular PHP scripting language and market-leading Apache Web server
to shorten application development cycles and accelerate time to market.
Great Bridge has packaged the WebSuite
release with a graphical installer, leading administration tools, professional
documentation and an installation and configuration support package
to help application developers quickly and easily deploy the power of
PostgreSQL for demanding business processes.
"We've seen tremendous interest in the
open source environment from businesses. We want to fuel that interest
by providing a ready platform for rapid development and deployment cycles
of large-scale, database-driven Web applications," said Robert Gilbert,
Great Bridge's president and chief executive officer. "WebSuite integrates
the best open source technologies for the Web into a single release
to shorten development cycles and speed time to revenue."
Joseph Mitchell outlines a way that PostgreSQL users can work around
the lack of a pure "no-locking" capability.
Quite a few people these days use Oracle in some capacity to store data
for applications. Anyone who is not an Oracle DBA (database
administrator), but needs to occasionally look into the database to see
whether data is being stored properly, is likely using sqlplus.
To set up environment variables to make access to the database easier,
many people will "su" to the Oracle user account (usually just
called "oracle"). Another way to do this is by putting the environment
commands into a file and source it.
#!/bin/sh
stty erase \^H
ORACLE_BASE=/u01/oracle
export ORACLE_BASE
ORACLE_HOME=/u01/oracle/product/8.1.6
export ORACLE_HOME
ORACLE_SID=mydb
export ORACLE_SID
PATH=$PATH:$ORACLE_HOME/bin
export PATH
LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export LD_LIBRARY_PATH
alias db='sqlplus user@mydb'
If you were to store a set of commands like this in a file and call
it "ora", then you could source it with ". ora" or "source ora" when
you need to check the database. In fact, if you're running more than a
single database instance, then you could set up a file for each of them.
Let's look at our environment after sourcing the file:
boson% env
HZ=100
PATH=/usr/bin:/usr/sbin:/usr/local/bin:/usr/ccs/bin:/usr/ucb:/
u01/oracle/product/8.1.6/bin
ORACLE_BASE=/u01/oracle
EDITOR=vi
LOGNAME=mylogin
MAIL=/var/mail/mylogin
ORACLE_SID=mydb
SHELL=/bin/ksh
HOME=/export/home/mylogin
LD_LIBRARY_PATH=/usr/lib:/usr/local/lib:/u01/oracle/product/
8.1.6/lib
TERM=xterm
ORACLE_HOME=/u0/oracle/product/8.1.6
PWD=/export/home/mylogin
TZ=US/Pacific
Now that our environment is set up, we can start up sqlplus with the db
alias established at the bottom of our ora file. The db alias contains
the name of the user we want to log in as and the instance name that we
want to work with.
Either of the two approaches to gaining access to Oracle (using
the "oracle" user account or logging in after sourcing your own file
will require you to enter a password. One of the benefits of setting up
your own file is the tidy way in which we can set up aliases for what
may be numerous database instances.
The "stty erase ^H" command depends on the particular terminal type you
are using. When in doubt (and when the current setting doesn't
backspace properly for me), I enter "stty erase" and then hit my
backspace key. I can then put whatever shows up my screen (e.g., ^?)
into my file.
I find setups like this are particularly useful when I find myself
working on numerous systems and need to keep track of a minimal amount
of information for each of them.
Checking Oracle with the tnsping Command
By Sandra Henry-Stocker
Two weeks ago, this column described the commands for setting up an
Oracle user environment. By capturing such things as the Oracle home
directory and Oracle instance into a file, you can easily adopt an
environment that makes using sqlplus and issuing SQL commands easy.
Just knowing a small number of SQL commands allows you to troubleshoot
a database connection and determine whether a database server is
running and managing data correctly.
An important file that you might also want to get to know is the
tnsnames.ora file. On a client, this file describes the various
databases with which the client may interact; on a database server, it
describes
the databases or "instances" that the server supports.
The tnsnames.ora file, which you should find in the directory defined
by $ORACLE_HOME/network/admin, will have look something like this:
DB01=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS=
(PROTOCOL = TCP)
(Host = 123.45.67.89)
(Port = 1521)
)
)
(CONNECT_DATA = (SID = DB01)
)
)
The parentheses should balance (i.e., an equal number of open and close
parentheses) but the way the parameters are broken across lines is a
matter of style. The parameters that you will be most interested in are:
* Host ? The IP address or fully qualified domain name of the
Database server;
* Port ? The port where the Oracle server should be listening for
database requests;
* SID ? The name of the Oracle instance.
The tnsping command can then be used to indicate whether a listener is
active on behalf of a database. Once you've set up your Oracle
environment (as described in the earlier column), you can issue a "ping
" command. If there are no problems, then you should receive
a response that says "OK" followed by the time required for the
response.
% ping DB01
Attempting to contact(ADDRESS=(COMMUNITY=DB01) (PROTOCOL=TCP)
(Host=123.45.67.89) (Port=1521))
OK (30 msec)
tnsping is especially helpful when you are unsure whether you are
connecting properly to a database. The netstat command may only show
you an ESTABLISHED connection:
boson% netstat -anP tcp | grep 1521
10.4.3.111:35793 10.4.3.5:1521 8760 0 8760 0 ESTABLISHED
The tnsping command, however, might indicate that the database is not
listening or that it cannot resolve the tns service, indicating that
it's time to check your tnsnames.ora file to ensure you are specifying
the correct information or talk to your Oracle database administrator
(DBA) to determine if there are any problems on the server.
To learn more about Oracle administration, you might want to check out
the slideshow available at
http://dclug.tux.org/bytes_1998_10_oracle/sld001.htm. Though this
slideshow addresses Oracle Database administration on Linux, it
applies, more or less, to Oracle on any Unix system.
If you work with systems that support or make use of Oracle, then
you'll likely benefit from knowing enough about database administration
to determine whether your databases are functioning properly.
McKinstry discusses various aspects of I/O that should
be considered when designing a system to run a database.
[Apr 04, 2001]
Open Source Databases Won't Fly by Nandit Soparkar (a faculty member
at the University of Michigan).
Read the article carefully. It provides no specific examples,
no example of production environment scenarios, no feature comparisons.
Only sweeping generalizations. Postgres and MySQL, they can be awfully powerful
in the skilled hands. Open source database may not be able to compete against
Oracle and Informix on high end mission critical applications (like a database
for an airport or bank accounts) but for a regular website MySQL and PostgreSQL
can be quite competitive. Also there is some overlap between file
systems and databases. Reiserfs looks like it is being extended to be a
database as well as a file system...
... ... ...
Now certainly, you will not disagree that commercial databases have
been wildly successful and time-tested. And in doing so, various problems
have been recognized and solved or resolved (eg consider data independence).
While open source systems are far newer and lithe, one still encounters
problems in using them; eventually, they too will come to be similar
in spirit to the commercial efforts: old wine in new bottles, or vice
versa? And then there is the issue of installed base of legacy systems.
Significant is my lament as regards the lack of rigor in coding
and programming. While there are relatively few well-accepted coding
standards, at least the importance of issues such as careful documentation,
checking for the logic in the programs written, maintenance of the versions,
all contribute to errors.
... ... ...
Having said all of the above, let me also mention that I believe
open source database systems will serve to provide the means to run
pilot projects, teach people, as well as for relatively small projects.
They will be wonderful in these cases, where one would not want to use
a heavyweight commercial system: where a flyswatter would work well,
a cannon would be disastrous Besides, it would be vastly cheaper—it
always boils down to the money, I am afraid. And now, I must find myself
a bomb shelter…
Ganesh Prasad
- Subject: Wrong, and for the following reasons ( Apr 17, 2001, 01:02:42
)
A superficial glance at the relative market share and technical features
of Open Source vs. commercial databases today may lead one to the hasty
conclusion that commercial databases will continue to rule. However,
there are many reasons why the tables could be turned in as little as
3 years.
These reasons (and they are mutually reinforcing) are progress, adequacy,
economy and network efficiencies. Let's analyse them separately first.
Progress: Remember the arguments against Linux two years
ago, before kernel 2.2? Linux wasn't scalable beyond two CPUs, it didn't
support large files or large amounts of RAM, it didn't have a journalling
filesystem, it didn't support USB, etc., etc. Most analysts then wrote
off Linux for those reasons. What do people think now?
Similarly, PostgreSQL doesn't support replication, its query support
isn't as extensive as Oracle's, PL/pgSQL isn't as rich as Oracle's PL/SQL,
it doesn't have very good graphical administration tools, etc., etc.
Do you think things will remain the same in 3 years? PostgreSQL 7.1,
released just last week, fixed several deficiencies of 7.0 (2k row limit
removed, outer joins supported, better support for complex queries,
more performance improvements and better admin tools). Things will only
get better.
Adequacy: It's often remarked that 90% of the market is
at the low end. That's why more Toyotas are sold than BMWs, though BMWs
are arguably "better". More people use products that are good enough
and affordable than use the very best products. With the release of
PostgreSQL 7.1 last week, PostgreSQL's claim to being "good enough"
just got a lot stronger. More people will find PostgreSQL to be adequate
for their requirements. Yes, Oracle or DB2 may still be "better", but
how will they fare against a "good enough" product that is free?
Economy: PostgreSQL will always remain free of charge.
That's not likely to be true of Oracle, DB2 or SQLServer. Those products
may be given away in some special deals (sales promotion), but they're
unlikely to remain free. When features converge, price becomes
an important issue, and PostgreSQL has a permanent advantage over its
commercial rivals.
Network efficiencies: Internet Explorer has had an enviable
distribution channel, -- the Windows CD --, which has led to its ubiquity.
PostgreSQL has an equally potent distribution channel -- the Linux CD.
PostgreSQL will penetrate every organisation penetrated by Linux. When
a good enough database comes free with your (free) OS, why hunt around
for another one with gratuitous extra features that you need to pay
for?
Now put these four factors together. A free database that is good enough
for most users and keeps getting better, comes to you free with your
OS. How long can you resist?
Prof. Soparkar's argument seems to imply that customers are fools. I
respectfully disagree.
Ganesh Prasad
Nick Mailer - Subject:
And in the real world.. ( Apr 17, 2001, 01:36:08 )
Well, I run a popular hosting/solutions company, and I helped hand-write
our whole support/customer service/billing/invoice/cashflow database
system in Perl and MySQL, and I can tell you that the system beats the
hell out of the big, bloated, expensive Oracle-type solutions. It's
been running brilliantly for about three years now, and we've never
had one single problem with the technology in that time. Yes, maybe
Postgre would be fun for having the RDBMS handle some transactioning
(but, to be honest, I am quite happy to handle that at the Perl side)
- but then Postgre is Free Software too :-)
So here you are, Mr Dataquest Professor Expert. I am a CEO in the Real
World running a Real World Company, and I can tell you _out right_ that
if Oracle paid _me_ money to run their system, I would not accept it.
Huge, bloated, hidden, unnecessary - that's exactly the sort of nonsense
I dispensed with when I ditched the Microsoft world (well, the X Window
System excepted, but life can't be perfect ;-)
|
Paul
- Subject: Re: Strange Attitude ( Apr 17, 2001, 02:30:09
) |
| >
> A lot of people writing this stuff are already being paid
to write code. Are they not going to use the same techniques
and expertise in their open source code? In fact, when you're
writing because you want to and have more freedom in how you
do it (instead of being management led) I would expect it to
be better.
>
Bingo. I think you nailed it exactly, people always excel
when doing something they enjoy. I think that those who write
open source typically take more pride in what they do, as their
names are attached.
|
|
Rupert Pigott
- Subject: Re: Roach Motel... ( Apr 17, 2001, 13:10:28 ) |
> The same dynamic applies to MySQL and PostgreSQL. Plus,
customers have the source code, so they don't have to wait for
the developer to fix the bugs. The point is, don't underestimate
the ultimate success of open source products.
This is why I hated "Profs" at Uni. While I was required to
substantiate my arguments they'd come up with some ill-researched,
unsubstantiated drivel and it would be law. Given that his vocation
and job *should be* founded on substantiated agrument we have
to question how he acquired his title.
For example, he FAILED to mention (I can only assume that this
is due to a lack of research) that INFORMIX was originally based
on Postgres (which is of course the forebear of PostgreSQL).
Obviously INFORMIX has is further down the line on this dynamic
than PostgreSQL, but I don't see any reason why it can't continue
to improve and acquire features just like it's commercial counterparts.
PostgreSQL has already got some unique features such as the
versioning system it uses for transactions, err, that's a *GOOD*
innovation, as opposed to a check box on marketing literature.
Prof. Pigott (Master of Unsubstantiated Drivel) |
http://www.sapdb.org/
-- a former closed source commecial "enterprise" level database now is open
sourced. SAP DB is the database engine behind one of the world's best known
enterprise applications systems is now under GNU licenses (GPL and LGPL).
You don't often get more enterprise mission critical that SAP R/3.
IBM developerWorks offers this tutorial on using Perl's
DB2 components.
"...we'll assume you have a x86 computer running Debian
GNU/Linux. We'll also assume you have a basic understanding of how to
use a UNIX shell, and that you have superuser access to the machine.
Everything in this example can be done remotely via ssh or telnet as
well as locally from the machine console."
"Bruce Momjian, Tom Lane and Jan Wieck, part of the
six-member international steering committee leading the development
of PostgreSQL, will assume senior-level positions at Great Bridge."
great LAMP distribution ( LAMP: Linux (operating system),
Apache (Web server), MySQL (database) and PHP (scripting language).)
Abria SQL Lite provides all the tools needed to develop
web-database applications in a quick and easy installation. Abria SQL
Lite includes MySQL, Apache Web Server, Perl, PHP, and phpMyAdmin.
Why and How I Installed PHP4, MySQL, and Apache on Windows 98
By Edward Tanguay Originally appearing in
The Web Developer's
Journal.
Four months ago I was selected as Web developer for
a large Web site project for six hospitals here in Berlin. It was going
to be an exciting project in which I would work together with a marketing
company to realize a network of Web sites. However, when we had the
first meeting, the head of the computer department said, "Wait a minute.
You program in ASP? We run Linux boxes here and can't justify the cost
or security risk of an NT-Server." They were unwilling to budge and
I didn't want to force Microsoft on them and then have to work with
disgruntled system administrators, so I backed out gently and declined
the contract.
Beyond ASP: JSP, ColdFusion or PHP
Since that day I've been looking for ways to extend
my server-side programming skills beyond the powerful but environment-limited
Active Server Pages. I concluded that I basically had three choices
outside ASP for server-side scripting: JSP, ColdFusion and PHP. The
first, JSP, is promising but still has a relatively small community
and a lack of ISPs which support it. In addition, if you use the JSP
server from Allaire it is quite expensive. The second choice, ColdFusion,
also from Allaire, provides you with a powerful and robust server-side
scripting environment centered around databases and e-commerce, but
again, the problem is it simply costs too much. If you don't have access
to the multi-thousand dollar Enterprise server, you're out of luck (the
free ColdFusion Express server doesn't even support session variables
- not very useful).
LAMP is red hot and free
PHP, on the other hand, is free. It is combined with
three other technologies to form a powerful Web development solution
abbreviated LAMP: Linux (operating system), Apache (Web server), MySQL
(database) and PHP (scripting language). Even with MySQL's insistance
that businesses pay a nominal fee for its use, Linux, Apache and PHP
are absolutely free which make the LAMP solution the most cost-effecive
solution for individuals, universities and businesses. Web devlopers
wishing to acquire valuable scripting skills can do so for free. On
top of that, LAMP is generally known to be more secure and robust than
other solutions (less crashing, less rebooting, inherent UNIX security).
On top of that, LAMP has one of the largest, most active, most dedicated
communities on the Web. PHP and the LAMP solution was clearly the best
choice for me and I was excited.
The Linux hurdle
However, if you are a Windows user, learning Linux
is like being parachuted into Italy without knowing Italian. You will
be hopelessly and thoroughly lost at the UNIX prompt. Although my repeated
forays into the Linux world have enabled me to install Suse Linux 6.4,
set up the X Window System and even use the vi editor,
I still lack so much basic knowledge of Linux that even the simplest
things such as unpacking files or installing software bring me to a
paralyzing stop. So even if you are the type of Windows user who can
buy a $50 computer book on almost any application and become an expert
in it by the end of the week, Linux is not something you are going to
learn in a week, or a month or even a year without an inordinate investment
of time, effort, paradigm shifting and lots of long, frustrating nights.
Hence LAMP is not an option for the casual Windows user wanting to learn
PHP scripting.
Settling for WAMP
Luckily, PHP, MySQL and Apache can also run on Windows
(hence WAMP). Note that this is not the most stabile platform to serve
your pages to the world, but it is an adequate solution if you want
to learn and begin developing with PHP/MySQL while remaining in your
familiar Windows environment. You can install Apache, PHP and MySQL
on your Windows 98 machine, then upload your applications via FTP to
a more stabile LINUX or UNIX server. WAMP is a good compromise for Windows-based
Web developers who want to expand their server-side scripting skills
into PHP and MySQL.
WAMP installation cryptic but not impossible
It took me a full seven days until I had Apache, PHP
and MySQL on my Windows 98 installed, configured and working. It was
not easy. The Web is full of well-meaning but conflicting manuals which
give you instructions such as to enter "/php4win/" when it should be
"c:/php4win/" - that one took me two days to figure out. Another two
days was spent downloading incorrect MySQL packets (the manuals tell
you to "download MySQL" but when you get to the page you are faced with
a choice of about 30 different download possibilities). Luckily there
were some very helpful people on the
Web Developer's List who kept helping me until I got it. Thanks
again to all of you!
Free workshop on installing PHP4, MySQL, Apache
on Windows 95/98
If you want to install PHP4, MySQL and Apache on Windows
95 or 98, take my
free online workshop on how to do it. This 14-step workshop
has explicit instructions and screen shots which will lead you past
all the pitfalls. With this Workshop, you can have PHP4, MySQL and Apache
installed, configured and working on your Windows 98 within the hour.
Edward Tanguay
is a Web developer and language trainer based in Berlin. For more diaries
and tips on development visit
Edward's Web Developer Site.
Someone please tell me that I will be able to use
large files painlessly on Linux sometime. Until then, run large databases
on name brand UNIX servers with name brand UNIX. Linux on x86 is good at
a lot of things, but a large database isn't one of them YET.
CaptainZapp
asks: "A customer of mine runs a ~30 GB data warehouse on a Sybase SQL
Server Database. Now their business requires a mirror of this database
in a different location. An offer by a reputed U/X vendor for the hardware
turns out to be about five times as expensive as when you get a reasonable
x86 box, with the necessary amount of disk space and, say, 1 gig of
memory. What does the esteemed Slashdot community think: Is Unix capable
of handling a database of this size and what other terrible pitfalls
do you foresee?" He's not worried about "mission-critical" here, he's
just wondering if it's possible.
Two words
(Score:4, Interesting)
by JonK (jonkale@yahoo.com)
on Wednesday July 26, @08:23AM EDT (#44)
(User
#82641 Info) |
| Bad Idea.
Changing RDBMSs is a Really Painful Experience
and one to be avoided at all costs if possible: it makes changing
OSes look trivial (hell, even upgrading from one point release
to the next can be a world of pain). If the data's already on
Sybase then for god's sake keep it on Sybase. Go for Sybase
on Linux, Sybase on SCO, Sybase on NT or whatever but remember:
it's a RDBMS and the underlying platform is effectively irrelevant
(pauses for flames as thousands of enraged Slashdotters start
to spout off and steam at the ears)
|
Re:Three words:with three
words (Score:4, Informative)
by arivanov on Wednesday July 26, @09:30AM EDT (#117)
(User
#12034 Info) |
| Very bad idea. Or maybe
even "Stupidity is limitless"
1. If you have not noted Oracle legal has
walked around every single site that had Oracle vs X benchmarks
(X=mysql, sybase, informix) and made them drop them. This is
actually possible under the 8.0x EULA. Actually just read the
EULA. It is a masterpiece in itself. You are not allowed to
benchmark the product and not allowed to question the fact that
it is fscking slow and not ANSI compliant. That is besides the
fact that if I was you I would not buy something where the manufacturer
intentionally disallows fair comparison with other products.
It is enough to say fsck this at least for me...
2. The original database is on Sybase. Sybase
is at least more or less syntactically ANSI SQL compliant. Oracle
is as far from ANSI as it gets. It will be a good guess that
it will take you ages to port the bloody thing. And porting
it will be more expensive than the "expensive" hardware.
3. I would see if the database design is implementable
under postgreSQL or MySQL on an Alpha. Alpha is cheap.
A reasonably good alpha is under 5000$. Storage will be a 1000$
more. This is as much as an appropriate x86 box. Postgres does
not have a 2GB database limit anyway as it splits database files.
MySQL does not have this limit on alpha because the platform
is 64 bit. Your problems are in the key limitation/lob interface
for postgress and transactions for MySQL.
4. If Neither of the solutions in 3 is implementable
you have to open wide you wallet and buy informix for Intel
or DB2 for intel. Both of them work and are ANSI compliant.
In btw DB2 for Intel linux developer edition is free. Free period.
No expiration. So you can actually see if the database will
work. And they match Oracle on some benchmarks and DB2 beats
the crap out of it when it comes to real scalability
and clustering.
@*** Baker's Law *** Misery no longer loves company. Nowadays
it insists on it.
|
30Gb databases
(Score:1)
by LinuxGrrl
(Rachel at LinuxGrrls dot Org)
on Wednesday July 26, @07:56AM EDT (#7)
(User
#123916 Info)
http://www.linuxgrrls.org
|
| Linux/IA32 probably not, at least under
e2fs as you'll likely hit the 2Gb filesize limit, depending
on how the database engine involved implements storage (Oracle
using its own data partition in "raw iron" style?). Linux on
other architectures, specifically the 64bit ones (Alpha, Sparc,
Sledgehammer and IA64 before long) would probably be fine. |
Re:raw partitions
(Score:4, Interesting)
by rnturn on Wednesday July 26, @10:46AM EDT (#183)
(User
#11092 Info) |
``oracle uses its own raw partitions/filesystem to
store its data. this speeds up oracle''
It doesn't have to manage it's own disk space. And it
may, under certain conditions, provide better performance.
We have been moving away from raw data partitions. This
after running some benchmarks of a large table residing on raw
partitions vs. the same data residing in tables in a filesystem.
The performance was actually better while accessing the
data in the filesystem. We're talking 10+% better performance
not just a few percent. Our experience, based on our benchmarks,
and discussions with Oracle technical people, is that the preference
for using raw data partitions was based on performance tests
using older versions of UNIX and less capable filesystems. Of
course, your mileage may vary.
Aside from performance, if your database changes frequently,
adding and deleting tablespaces is a major pain (with long downtime)
when you're using raw data partitions but is a snap when you're
using filesystems for data. If your database is fairly static
raw partitions might buy some little bit of performance but,
again, at the expense of managability. IMHO, raw data partitions
just aren't worth it. Even if comparitive performance were a
wash, the easier means of managing the database weighs in favor
of filesystems.
|
Oracle officially recommends
against RAID ... (Score:4, Informative)
by Nicolas MONNET
(nico@nospam.monnet.to) on Wednesday
July 26, @11:52AM EDT (#225)
(User
#4727 Info) http://monnet.to
|
| seek times are dramatically improved
in most (if not all) RAID levels Seek time is not going
to be any better in mirrorring, for one. The two heads reading
the same data won't go faster than one head, will they?
Then for striping, this usually won't make any kind of difference
since data access will be randomly spread over the disk. So
there you go.
NOW smartly organizing the database WITHOUT striping amongst
several disks *will* make seek times faster, actually, it will
require less seeking. A typical Oracle installation (as recommended
by Oracle) will have for example the software on one disk, the
indexes on another, and the actual data on a third.
Now since one DB transaction requires typically at least
one index lookup and one data retrieval, which are unlikely
to reside close to each other on one disk. Now when they're
separated on two disks, subsequent queries will have less seek
time .
Now, since I was right, will you give me my karma back? ;)
|
"The current version (ASE 11.0.3.3 ESD#6) package
is different from previous distribution of ASE 11.0.3.3. The new package
separates ASE and OC/OS SDK. Also OC/OS is now available for Alpha platform.
You can also find FreeBSD version of the package. Intel, Alpha and FreeBSD
releases differ only in packaging and they are all built from the same
source."
"Software is FREE for development as well as Deployment.
The License Agreement has been updated to include FreeBSD platform."
"Adaptive Server Enterprise 11.0.3.3 ESD#6 for Linux
runs on Linux systems with Kernel 2.2.12 and glibc-2.1.2-11 installed.
Verify that your system meets the hardware/software requirements before
installing the software."
a tool for working with SQL Server and probably Sybase databases.
Suggested by Boris Denin
"Inprise/Borland (Nasdaq: INPR) today
announced the availability of the source code for InterBase 6.0, its
cross-platform, standard query language (SQL) relational database management
system (RDMS). Binary formats for the Linux, Windows and Solaris operating
systems are also available for download, free of charge. With Version
6.0, Inprise/Borland introduces performance improvements, a higher level
of SQL92 compliance, and a replication engine."
FREMONT, CA -- (INTERNET WIRE)
-- 29-06-2000 -- AbriaSoft(TM), a provider of database solutions, announced
today the launch of its new distribution, Abria(TM) MySQL Lite. AbriaSoft
is an Open Source company founded to provide packaged MySQL(TM) support
and software with development and administrative tools to the public.
MySQL is an Open Source database, originally developed by T.c.X Consulting
in Sweden.
Abria MySQL Lite significantly reduces
the cumbersome process of installing and configuring MySQL and Apache
into a quick installation of less than ten minutes. Abria MySQL Lite
offers an integrated, turnkey install of RedHat Package Modules (RPMs)
for MySQL 3.22, Apache Web Server, PHP3 and Perl. PhpMyAdmin, a tool
for web-based MySQL administration, is also included. AbriaSoft's distribution
runs on the RedHat 6.x platform. Versions for other
distributions will be
released later in the year. Abria MySQL eases the installation of all
these components on a RedHat
server, and makes sure that all dependencies and files install correctly.
Older versions of MySQL are automatically upgraded to newer versions.
"This distribution of MySQL is the first
of its kind," says Mark Gerow, Project Manager for AbriaSoft. "We believe
this will further establish MySQL as the premier choice for a back-end
database for Web servers."
Abria MySQL Lite is available for unrestricted
free download from AbriaSoft's Web site (www.abriasoft.com).
News, information, and FAQs on Abria MySQL, and on other Open Source
products are also available at the site. As an added service to MySQL
fans, AbriaSoft will be offering computer-based training (CBT) courses
on CD-ROM for MySQL. These interactive training CD-ROMs will be available
for demo and purchase on the AbriaSoft Web site in July 2000. AbriaSoft
will be shipping its "Standard" and "Professional" editions within the
next two months, offering SSL, WebMin, AbriaSoft MySQL Admin Tool, GUI
Admin Tools, and all the components necessary in quickly install and
run a high performance MySQL web site.
The current (DBI/DBD) version is 2.1.8. (Released Apr 01 2000)
The current (SybPerl) version 1.6.8. (Released December 14 1999). From program
description:
DBGUI
is a complete X graphical database interface that can -
- perform any SQL command
- save the SQL results to a file
- perform incremental or standard
searches on the SQL results
- keep a configurable history of _all_
SQL commands and parameters
- run on any UNIX or UNIX-like machine
(Linux)
- sort (normal, numerical and reverse)
on any column of the SQL results
- print the SQL results to a printer
- quick command line clear and restore
for easy command line generation/pasting
- "clone" the results to a new display
window for comparisons etc.
- utilize the DBI/DBD (or SybPerl)
libraries or isql/sqsh binaries for the queries
- maintain four complete configuration
"snapshots" for easy retrieval
- reload the last set of parameters
on startup
- interactively enable and disable
three different command lines for execution
- display the column data type in
each column header
- display the column width in each
column header
- solicit and quickly popup a list
of the system datatypes. (SybPerl Version)
- indicate a busy/idle condition with
a colored indicator (red/green)
- display the date/time of the last
command execution in title bar
- load a specified checkpoint file
on startup for pre-defined menu histories
- display the checkpoint file path
in the title bar
- color code header info in multiple
result-set data.
- quickly sum up any numerical data
column.
- more probably....
"The financial wizards who successfully
launched Red Hat, the celebrated Linux vendor, are now targeting the
lucrative database market as the next fertile ground for capitalizing
on open-source development."
"Frank Batten Jr.'s Landmark Communications,
which took Red Hat public last year, launched subsidiary Great Bridge
LLC to market commercial products and services supporting open-source
database PostgreSQL."
"PostgreSQL has more than 1,000 developers
worldwide and competes with leading proprietary databases such as Oracle
and Microsoft SQL Server. Great Bridge's launch coincides with the release
of PostgreSQL version 7.0, which adds performance improvements, new
querying capabilities, among other enhancements."
"If you've been having trouble installing Oracle8i, be bewildered
no longer. Uche Ogbuji explains why Oracle8i is one of the wildest,
wooliest installs ever, and how to work around its quirks."
New chapters on numbering rows, combining selects, data types, transactions
and locks.
Have you been trying to install DB2 Universal Database
on a Linux-based workstation? Did you run into some troubles? As I scan
around the DB2 and Linux newsgroups, I hear from many of users who are
getting frustrated when trying to get DB2 running on Linux. How did
this problem get so big? Well, the Linux phenomenon is relatively new
and ever-changing. Recently, all sorts of vendors are flocking to market
their distributions, with slight differences between them all. Combine
that with what seems to be quarterly releases and you can see how communication
channels between the Linux vendors and the people who build applications
to run on them get clogged. While efforts are being made between application
developers and Linux vendors to define this communication pipe, you
can use the information in this article to get yourself up and running
in no time at all.
DB2 for Linux is officially supported on the following
Linux distributions: Caldera OpenLinux, Red Hat Linux, TurboLinux and
SuSE Linux. This article will take you through the steps involved in
installing DB2 on each of the supported Linux distributions. In the
article, I assume you have not previously installed a version of DB2
and you are not maintaining any of the default users created by a default
DB2 Installation. The three user IDs that will be created during a DB2
installation are: db2inst1, db2fenc1 and db2as. If you have any of these
users on your system, be sure to remove them and their associated directories
before installing DB2. This article also assumes you are familiar with
the rpm command, used to install packages. If you are not familiar with
this command, refer to your Linux documentation.
Complete Story
InterBase 6.0 is a powerful, high-performance cross-platform
relational database designed for business-critical, mobile computing
and Internet-based applications on Linux, Windows NT, Solaris, and other
UNIX operating systems."
Linux Planet
offers two tutorials on setting up a MySQL based website
part 1 and
Part 2.
Press release:
Dec 10th, 17:50 UTC
The graphical applications use the GTK widget set and are released
under the GPL.
GODFREY, Illinois, Dec 10-- The Orasoft team is happy to announce
the official stable release of the Oracle Applications for Linux suite.
We believe that it is the most advanced database software available
on the Linux platform for using and managing Oracle databases. The graphical
applications use the GTK widget set and are released under the GPL.
Source code and RPM executable are located at the Orasoft website (http://oracle.mattshouse.com).
Included in the suite are the following applications:
Procedit: - A visual procedure/function/package editor.
SqlWork: - A database querying tool similar to SQL Worksheet.
Session Monitor: - A DBA snooping tool to view user activities.
TableBrowser: - A table viewing/manipulating application.
The release follows a lengthy beta period in which over 1000 people
participated.
Version 3.0 of the suite was released Dec. 9 and is now available for
free (RMS) download.
CONTACT: Matthew Chappee
matthew@mattshouse.com
Orasoft Team Leader
http://oracle.mattshouse.com
I have completed through chapter 6 of my book, "PostgreSQL:
Introduction and Concepts." Of course, this is only the first draft.
It can be viewed at:
http://www.postgresql.org/docs/awbook.html
There are major improvements in this version. I have
added sections to chapter 4 dealing with DEFAULT and functions/operators.
The figure placement and font usage is improved.
Chapter 6 covers using multiple tables and joins.
This was a very difficult chapter for me to write.
-- Bruce Momjian
http://www.op.net/~candle
maillist@candle.pha.pa.us
"The Oracle connection comes from the two companies'
plan to promote Oracle 8i on Linux. The notoriously anti-Microsoft database
giant is already pushing its product hard as a key e-commerce tool on
Windows, and by working with Red Hat it can provide a solution that's
entirely Windows-free."
"To that end, Red Hat will build stronger links to
Oracle 8i into its Linux distribution. The journaling file system, which
records file operations, making recovery from crashes a less haphazard
-- and therefore faster -- operation, a new volume manager and support
for greater amounts of RAM all help here too."
TDAN Archive
-- The data base administration newsletter archive.
[Feb 10, 1999]
DBMEdit
A user-friendly Web-based DBM database editor
(in Perl 5)
Notes:
- This is a Spartan WHYFF (We Help
You For Free) site written by people for whom English
is not a native language.
Some amount of grammar and spelling errors should be
expected.
- The site contain some broken links
as it develops like a living tree...
Please try to use Google, Open directory,
etc. to find a replacement link (see
HOWTO search the WEB for details). We would appreciate
if you can
mail us a correct link.
|
|
|
|
In case of broken links
please try to use Google search. If you find the page please notify
us about new location
****
Open Directory - Computers Programming Databases
**** Pal's
Linux RDBMS Library
This site is a compilation of the best free online
readings about relational databases on Linux. If you're a Linux RDBMS/database
administrator, a database designer/developer, or simply a Linux user
with database ambitions, you'll find links to valuable resources here:
articles, papers, and books on various aspects of relational database
management. Needless to say, much of this material is more or less applicable
to other (UNIX) environments, too.
Ingres - Frequently Askes Questions
DB Resources
Bruce Momjian's Writings
The Open Web Application
Security Project
MYSQL.
- AbriaSoft - Software for An Open World -- great LAMP distribution
( LAMP: Linux (operating system), Apache (Web server), MySQL
(database) and PHP (scripting language).)
Abria SQL Lite provides all the tools needed to develop
web-database applications in a quick and easy installation. Abria SQL
Lite includes MySQL, Apache Web Server, Perl, PHP, and phpMyAdmin.
-
Simple
Perl Databases. An online tutorial with example perl scripts for
maintaining and searching a simple database. Brent Michalski.
-
PostgreSQL
-- a robust, GPLed Object-Relational DBMS (ORDBMS), derived from the
Berkeley Postgres database management system. PostgreSQL v6.4.2
released for Linux. PostgreSQL is a SQL server which is the "default
SQLserver" shipped with most Linux distributions. For more information:
http://www.postgresql.org/.
- MySQL -- a good SQL
database server with strong Web connectivity. The choice of
SNDP project. Now GPLed.
Etc.
- CQL++
-- an ANSI and ODBC compliant database management system. GPLed for
Linux only.
-
Beagle
SQL -- a free client server SQL database engine
-
LEAP -- a relational database management system using the relational
algebra as its query language
-
locus
-- Personal project: fulltext database supporting complicated queries
-
SQSH -- SQL shell -- a replacement for the venerable 'isql' program
supplied by Sybase
-
Sybase -- Adaptive Server Enterprise is now available on
the Linux operating system - for
FREE. The package includes the standard
features of Adaptive Server Enterprise and all related connectivity
components. It's offered as a free, unsupported release for development
as well as deployment. Initially, the software will be available through
leading Linux distribution vendors,
Red Hat
,Caldera
Systems and
S.u.S.E,
Inc..
-
ADABAS D for LINUX -- a high performance relational DBMS. Free for
non-commercial and personal use. Caldera ship it with Open Linux.
-
Informix on Linux -- developer edition is free. Here are some useful
links from the
Intraware
site:
Etc.
-
mSQL
-- a non GPLed lightweight database engine offering a subset of the
ANSI SQL specification as its query language. Licence is more restrictive
that MySQL licence.
Unofficial mSQL
FAQ
-
Essentia -- a Database Server of InterSoft developed with a new
Technology: RISE (Reduced Instruction Set Engine)
-
Flagship -- a superset of Clipper 5.x and Dbase III for Unix. Visit
the
FlagShip for Linux ELF entry at LinuxMall -- you can download the
slightly limited, but functional FlagShip "Test Drive" for Linux ELF
directly. Includes a complete Online Manual.
- YARD
-- a database server for UNIX operating systems -- private edition is
free.
Etc.
SS-File Database Manager -- flat file database
Copyright © 1996-2008 by Dr. Nikolai Bezroukov.
www.softpanorama.org was
created as a service to the UN Sustainable Development Networking Programme (SDNP)
in the author free time.
Submit
comments This document is an industrial compilation designed and created
exclusively for educational use and is placed under the copyright of the
Open Content License(OPL).
Original materials copyright belong to respective owners. Quotes are made
for educational purposes only in compliance with the fair use doctrine.
Standard disclaimer:
- The statements, views and opinions presented on
this web page are those of the author and are not endorsed by, nor do they necessarily
reflect, the opinions of the author present and former employers, SDNP or any other
organization the author may be associated with.
- We do not warrant the correctness of the information provided or its
fitness for any purpose
- In no way this site is associated with or endorse cybersquatters
using
the term "softpanorama" with other main or country domains (e.g. softpanorama.com) with
bad faith intent to profit from the goodwill belonging to
someone else.
Last modified:
November 09, 2008
(Score:5, Interesting)
(http://www.feratech.com/about/bios/ahochber/)