 |
SQL Databases for Linux
by Christopher Browne, in Category Reviews - Sun, Sep 30th 2001 00:00 UTC
So you find you need to store some data on your Linux system, and are
wondering what program to use... There are a vast array of database
systems available for use on Linux. Some are simple, some
sophisticated, some cheap, some expensive. One of the first things you
ought to do is ask yourself what you need to do with the database,
as that is critical to bringing the number of choices down from
"stupendously bewildering" to merely "astoundingly many."
Copyright notice: All reader-contributed material on freshmeat.net
is the property and responsibility of its author; for reprint rights, please contact the author
directly.
Different database systems offer substantially different ways of
storing and retrieving information, and deciding what to use will
require asking yourself questions about storage and retrieval.
This set of articles tries to explore this by looking at three
"classes" of database systems. This article will deal with SQL
Databases. A followup article will discuss xBase descendants and
Keyed Tables like DBM.
One of the most common database questions that come up is "Where is the MS Access
clone?"
One thing that should be made clear is that there isn't such a thing. "Experts" tend to regard this
as a good thing, as MS Access tries to do
too many things all at once to be really particularly successful at
them all.
MS Access combines:
-
Data storage and retrieval,
-
A multiplexing data access system,
-
A report writer,
-
And a form generator for data entry and other
interactive data manipulation.
Most of the database tools that will be described here primarily
focus on the first area. By doing just storage and
retrieval, reliability certainly increases.
Mind you, there would be considerable merit in having a single
integrated environment for data access, writing reports, and providing
GUIed access to update data. There are ongoing projects to provide
those sorts of capabilities, but none are yet quite as "GUI-pretty and
newbie-friendly" as MS Access.
SQL Databases for Linux
There are a lot of database systems that run on Linux using
SQL data access schemes.
These databases are often fairly "heavyweight", requiring
considerable disk and memory resources and providing data access
capabilities of considerable sophistication.
Since they provide a (somewhat) common query language, a
suitably-designed application may be able to be readily ported to run
on different database systems, thus allowing the ability to take
advantage of differing performance properties, as well as the ability
to not be forcibly dependent on any one vendor.
Unfortunately, virtually all SQL database systems offer one
"extension" or another that tends to tempt developers to tune their
applications specifically for one database system.
One of the benefits of the use of a reasonably "abstract" query
language is that the database engine can do a lot of work for you. For
instance, rather than having to write code (adding temporary
variables, loop structures, and such) to access several tables, you
may construct a more complex SQL query that lets the database engine
join tables together for you. The hopes typically expressed are that:
-
You avoid writing the code to "physically" access the
data, thus saving time, energy, and debugging effort.
-
The DBMS may do a better job of optimizing the
accesses than you would, and you get to avoid the
debugging effort, similarly to the way C may be more
productive for programming than assembly language.
-
If performance is poor due to the DBMS not having
suitable table indexes, you can add an index and watch
performance improve substantially without having
to write any code. Similarly,
adding some memory for table buffering can improve
performance without your touching a single line of
code.
-
In most cases, the database server processes support
transaction capabilities, improving reliability.
In practice, software bugs are sufficiently ubiquitous that you'll
still need to do some debugging, and there are certainly some
overheads in terms of the cost of parsing queries and submitting them
to the DBMS engine.
Other notable merits of SQL database systems include:
-
They offer the ability to readily add additional kinds
of data to the system.
-
They almost always offer the ability to permit
multiple users and/or processes to have robust concurrent
access to data.
-
Most offer a considerable degree of transactional
robustness typically not offered
by less sophisticated alternatives through the four Transaction
Processing requirements, called ACID:
- Atomicity
-
All transactions are either performed completely
(committed), or are not
done at all; a partial transaction that is aborted
must be rolled back.
- Consistency
-
The effects of a transaction must preserve
required system properties. For instance, if funds
are transferred between accounts, a deposit and a
withdrawal must both be
committed to the database, so that the accounting
system does not fall out of balance.
In double-entry accounting, the "staying in
balance" property is usually not overly difficult
to maintain. The more thorny issue comes when the
property is something like "Cash Balance Cannot
Drop Below Zero", or "We can't ship inventory
we don't have." In such cases, if you have two
transactions being submitted concurrently, it could
be that either could be
accepted, but not both. If
one of the transactions would cause balance
requirements to be violated, the transaction
management system needs to reject one of
the transactions.
- Isolation
-
Intermediate stages must not be made visible to
other transactions. Thus, in the case of a transfer
of funds between accounts, both sides of the
double-entry bookkeeping system must change
together for each transaction. This means that
transactions appear to execute serially even if some of
the work is done concurrently.
- Durability
-
Once a transaction is committed, the change must
persist, except in the face of a truly catastrophic
failure.
If Mongol hordes ride through and lay waste to
your server room (or, sadly more likely, an
unfortunate plane crash takes place), you can
hardly expect a transaction system to guarantee
that all is well, but a good transaction processing
system should be resistant to moderately traumatic
sorts of system
failures such as a network link breaking down or
perhaps even something as traumatic as a disk drive
malfunctioning.
There are really a lot of SQL database systems available to run on
Linux. At one time, there was great excitement at the thought that
some people at Oracle had an internal port they were fiddling with;
today, almost any database vendor that offers a version on some
Unix variation sells licenses for Linux. Pretty much the only
major database vendor that doesn't deploy a version on Linux is
Microsoft. The top tier industry names, Oracle, Sybase, Informix, and IBM DB/2, are all
available on Linux.
Development licenses are typically available inexpensively or even
for free, but production licenses tend to be quite expensive. These
systems tend to be "heavyweights" in terms of feature sets, use of
memory and disk, and licensing costs. They provide robust access to
large amounts of data, at considerable price; if you are building an
"enterprise" system, they are the common choices.
They are not suitable for every purpose; other database systems
often offer superior characteristics in one area or another.
-
If you want an "Open Source" database system, a number are
available.
-
There are a number of commercial databases
specifically targeted as "embedded systems".
These database systems tend to put the data in a
single compact location, whether that be in files in a
single directory hierarchy, or even in a single file.
If the database is to be used as part of an
application, it is attractive if the data stays highly
localized in contrast with some of the DBMSes with which, to
maximize speed and robustness, the system may manage raw
disk partitions.
-
In-memory databases take advantage of huge amounts of RAM
and try to provide particularly fast query and analysis
capabilities.
-
There are also other special purpose databases
providing more advanced capabilities for text
searching and the like.
Open Source SQL DBMSes
A number of database systems are available under Free Software
licenses such as the GPL. Most notable are MySQL and PostgreSQL, which
are available pre-packaged for many Linux distributions, and which are
widely used to support Web-based applications.
There are a number of others; they include "toy" databases as well
as some that used to have proprietary licenses. Firebird was once Borland InterBase, and SAPDB was once called Adabas-D. These may become of greater interest in
the future, once "systems integration" efforts get further along, but
they are not yet being widely integrated with Linux distributions or
with Free applications.
-
MySQL
-
PostgreSQL
-
Firebird
-
InterBase
-
SAP
-
SQLite -- an SQL database
implemented atop GNU gdbm.
-
Gadfly --
an SQL Relational Database in Python
-
kshSQL
DBMS -- an "SQL simulator" written using the Korn
shell. Performance is doubtless not going to be
spectacular, but if it's easy to install, it might be
useful for some applications.
-
GNU SQL
-
Beagle SQL -- formerly at http://www.beaglesql.org/, but this
software hasn't been sighted in the wild in a couple of years...
Embedded SQL DBMSes
"Embedded" databases are intended to be embedded in applications,
and tend to be designed with a view to being easy to install and
to require little, if any, attention to administration or tuning.
In-Memory SQL DBMSes
These database systems store data primarily in memory, as compared
to more traditional architectures that involve "paging" data from disk
as needed. At first glance, this would appear a crippling reduction in
robustness, but reality lies elsewhere, as nothing prevents these
systems from being tremendously "paranoid" in logging updates to more
permanent storage.
The point of the exercise here is not so much to provide robustness
as it is to take advantage of the fact that memory space on modern
computers has grown astoundingly. These systems assume that
enough physical RAM is available to hold the entire database, which
means that queries proceed without worrying about what is or isn't
cached.
In-Memory databases should be particularly useful for applications
like data warehousing, or for providing fast responses for
things like catalog queries.
Miscellaneous SQL DBMSes
There are many, many commercial database systems available on
Linux; it is quite difficult to distinguish, for many of these
systems, why they should be considered interesting, as many represent
"Yet Another SQL DBMS conforming to some reasonable set of
standards". Some have, as "claims to fame", the ability to do
sophisticated text-oriented queries, or integration between a database
server and a Web server.
Data Access
It's not much use having a neat new database system if you have no
way of querying or updating information in that database, working in
some language you find desirable to work in.
There are a number of common ways to access data in an SQL
database. Some are standardized to the point that it is not difficult
to plug in a different database system as needed; others are less so.
- ODBC or SQL/CLI
- There are standards for this "gateway into SQL";
ODBC seems to commonly be regarded as a Microsoft-only
sort of "database driver" for Windows, but there do
exist ODBC implementations for Linux that essentially
provide an API designed for use in C. Since many other
languages interface readily with C libraries, this is
fairly widely usable to allow access to databases.
- JDBC
- JDBC is fairly consciously targeted as a sort of
"ODBC for Java". It provides a set of Java APIs for
accessing databases, and with the widespread use of
Java for Web application servers, many database makers
provide JDBC "drivers".
- ESQL
- This stands for "Embedded SQL". Database systems
often used to use this as a way to help write database
applications in C. You would write programs mostly in
C, with SQL queries embedded as needed. The C program
would then be run through an "ESQL processor" that
would transform the embedded SQL queries into C code to
express those queries.
This should allow programs to be written more
compactly and to be more efficient than would be the
case with SQL/CLI. Unfortunately, this requires
work in two languages simultaneously, and tends to be
somewhat nonportable.
- Perl DBI
- The idea behind Perl DBI is to provide a high-level set of
functions for manipulating databases that work for
many different databases, and then to link those functions to
database-specific code (perhaps using ODBC or some
database-specific library typically implemented in
C).
Aficionados of Python or Tcl can find
similar sorts of libraries; the somewhat more
widespread popularity of Perl means that there are
somewhat more options for Perl.
Performance
Note that this discussion has not even tried to address the
issue of which database system is "fastest."
The problem is that evaluating this is a really daunting
problem. People commonly claim that one DBMS or another is much faster
than the others. Unfortunately, this sort of thing is very difficult
to evaluate in a scientific manner. We might find that MySQL was providing vastly better performance
than PostgreSQL, until an extra table key
or buffer was added, at which point the tables might turn. Or you
might find that PostgreSQL has some
feature to support your application that no other database system
offers, so it is the only option that actually performs acceptably.
Unfortunately, when it comes to benchmarks, everyone is
pretty partisan. Database vendors have often been known to send
developers out to work with hardware vendors to tweak performance on
industry benchmarks, and there is some indication that vendors have
even tuned database engines to be specifically aware of certain
benchmarks, in much the same way that compiler vendors were once
accused of writing code to recognize the Byte Magazine Prime Number
Benchmark, and then generate hand-tuned assembly
language. Faking a benchmark like that obviously goes well past
what is reasonable, but things gets less clear if you find that using
some vendor-specific extension dramatically improves performance on
some part of a benchmark.
The way that locking is used can be easily cited as a place where
performance will vary; a major merit of the more sophisticated
database systems is that they cope well with having many users working
with and modifying data concurrently. Correctly handling that
requires doing some locking of data against modification. There are
several different granularities of locking, with different costs and
benefits:
-
A table or whole database lock is really cheap to
implement, but is rather "antisocial", as it means that
only one user/process is granted access, and all others
are blocked.
There are times when this is reasonable or even
necessary, particularly when data
conversions or cleanup are underway, but if a system
has a lot of users trying to do updates, they'll get
extremely irritated at being blocked from doing their
work.
-
At the other end of the spectrum is the "row lock",
in which what is locked is one row in one table.
This is definitely a lot less "antisocial"
than the table lock; anybody being blocked by a row
lock is likely trying to modify the very same bit of
data as someone else.
Unfortunately, the DBMS has to manage and track these
locks, so behind the scenes, there will be a (probably invisible
to the user) lock table with one entry for each row that is
locked. If there are a lot of table rows being worked
on, that's a lot of locks!
-
Sybase was long criticized for not offering row
locking; they instead offered page
locking, which is an intermediate between row and
table locking.
In many database systems, space is allocated to
tables in "pages", where a page is a fixed block (often
2K) of storage devoted to rows in a specific table. If
each row is 80 bytes, a 2K page would hold
about 25 rows.
Sybase would lock the whole page, which is less "antisocial" than
locking the whole table, but if you're merely modifying
one row, the lock might affect 25 times as much data
as is necessary.
Careful design of the application can keep this from
being a problem. For instance, users might queue
updates and submit them to a centralized "transaction
update" process. If that central process is the only
thing updating the tables, the page contention goes
away, and there may be a performance increase because
this form of locking is a bit less expensive than row
locking. But it does require careful application
design...
Another daunting issue is that a number of notable
commercial database systems have, as a specific license
clause, the condition that you are forbidden to publicly
report performance benchmarks.
Author's bio:
During his University years, Christopher Browne was
employed by three public accounting firms as a student in accounts,
preparing many sets of tax returns and financial statements. As a
result, he decided he definitely didn't want to be an accountant or an
auditor, but he's used his knowledge to pursue a career in programming
financial systems. He has done more writing in recent years,
publishing several articles and co-authoring the book Professional
Linux Programming.
T-Shirts and Fame!
We're eager to find people interested in writing articles on
software-related topics. We're flexible on length, style, and
topic, so long as you know what you're talking about and back up
your opinions with facts. Anyone who writes an article gets a
t-shirt from ThinkGeek
in addition to 15 minutes of fame. If you think you'd like to try
your hand at it, let jeff.covey@freshmeat.net
know what you'd like to write about.
[Comments are disabled]
Comments
[»]
sqlite-gdbm dependancy
by PyHedgehog - Aug 12th 2007 19:40:00
You are writing "SQLite -- an SQL database implemented atop GNU
gdbm". Is it true?
For all that I know, SQLite's best approach is independency of 3rd-party
libraries. May be you mean, that first implementation uses gdbm compatible
format? Could you provide more info?
BTW: Shouldn't articles in this category be reviewed and update from time
to time?
[reply]
[top]
[»]
ScimoreDB
by Marius Slyzius - May 30th 2006 11:36:39
http://www.scimore.com
Here is another Distributed SQL RDBMS, which offers both client/server and
embedded database system.
[reply]
[top]
[»]
MySQL, The Toy DB?
by Mike Robinson - Dec 14th 2001 16:01:31
That's hilarious. A toy database.
What an awesome choice of words.
How much was that oracle license again?
:)
--
.mike
[reply]
[top]
[»]
InterBase
by David M Blackburn - Oct 31st 2001 03:49:15
InterBase is well worth a look, we have been developing client/server apps
on InterBase for about 4 years now. For the front ends we use Delphi
(Windows),Kylix(Linux) or in some instances PHP. Overall we have found
InterBase to be one of the most reliable SQL engines that we have come
across, take a linux server with reiserfs filesystems, put Interbase on
there and run an application against it pushing lots of transactions per
second, now remove the power lead! ( Anybody want to try this with their
sysems, just joking. You could try it on a dummy server though just to see
).
When rebooted Interbase automatically recovers with the database back on
line, usually takes about a minute for a full system re-boot, how many
other systems could you do this to? Makes one hell of an impressive
demo.
( Perhaps that is why Interbase is used in the M1 tank, yes really. )
Version 4.0 is free on Linux ( Needs an early version, we use SuSE 6.0
)
Version 6.01 is free & open source ( Linux & Win )
For connectivity(ODBC etc) & tools, try the Phoenix web site ( The
people who run this site used to be ISC )
Closing thoughts, InterBase has never had the sort of marketing that the
so called commercial leaders have. Give it a try, I'm sure you'll be
suprised.
[reply]
[top]
[»]
XA Complient ?
by The Cydergoth - Sep 30th 2001 18:04:11
This is great, as recently I was looking for a free SQL database under
linux - postgresql was the main contender. The major question which we
could not easily find answers to was XA complience and free TP
monitors for linux. Postgressql seems to have an XA complient JDBC
driver.
Any comments ?
[reply]
[top]
[»]
Linux SQL - All Revved Up, with no where to go.
by projects - Sep 30th 2001 16:46:34
While there are many enigines for Linux, form packages are few and far
between, and web based forms packages even rarer.
An engine is necessary, but you need car to put it in. I've probably
missed some great package out there, but it seem to me SQL on Linux is
sorely lacking any decent forms packages.
[reply]
[top]
[»]
Re: Linux SQL - All Revved Up, with no where to go.
by dan - Sep 30th 2001 21:19:39
> While there are many enigines for Linux,
> form packages are few and far between,
> and web based forms packages even
> rarer.
>
> An engine is necessary, but you need
> car to put it in. I've probably missed
> some great package out there, but it
> seem to me SQL on Linux is sorely
> lacking any decent forms packages.
Dude you obviously didn't read my post re: Kylix, just above
your own. Try it out. It's available for free, and it kick's Access's
butt...
[reply]
[top]
[»]
Re: Linux SQL - All Revved Up, with no where to go.
by Ray Shaw - Nov 16th 2001 03:27:40
> While there are many enigines for Linux,
> form packages are few and far between,
> and web based forms packages even
> rarer.
>
> An engine is necessary, but you need
> car to put it in. I've probably missed
> some great package out there, but it
> seem to me SQL on Linux is sorely
> lacking any decent forms packages.
For web-based stuff, I prefer to use PHP4 with PostgreSQL. It's certainly
much better than the horrible, proprietary Developer 2000 that Oracle wants
you to use.
--Ray
[reply]
[top]
[»]
HSQLDB
by ted stockwell - Sep 30th 2001 11:22:26
http://www.hsqldb.org
hsqldb is a relational database engine written in JavaTM , with a JDBC
driver, supporting a subset of ANSI-92 SQL. It offers a small (about 100k),
fast database engine which offers both in memory and disk based tables.
Embedded and server modes are available. Additionally, it includes tools
such as a minimal web server, in-memory query and management tools (can be
run as applets or servlets, too) and a number of demonstration examples.
Downloaded code should be regarded as being of production quality. The
product is currently being used as a database and persistence engine in
many Open Source Software projects and even in commercial projects and
products! In it's current version it is extremely stable and reliable. It
is best known for its small size, ability to execute completely in memory
and its speed. Yet it is a completely functional relational database
management system that is completely free under the Modified BSD License.
[reply]
[top]
[»]
Mimer SQL
by Twinkle - Sep 30th 2001 09:01:45
Let me just add Mimer SQL to the list of RDBMS:
http://freshmeat.net/projects/mimer/
[reply]
[top]
[»]
Kylix
by dan - Sep 30th 2001 04:48:57
I think you're missing one VERY important solution: Kylix.
It's delphi for Linux (by Borland for those who don't know).
It is available for free for those making GPL'd software. I bought the
'Desktop Developer' while it was on special for $AUS500, and it was worth
it, if just for mucking around with.
Kylix has an embedded SQL-compliant database engine, and also can link to
other databases (natively to mysql, and you can use unixODBC for
others...).
The IDE currently runs (and crashes) under wine, but they apparently are
working on a native Linux IDE. The compiled code, however, is native 32-bit
ELF format, and is quite fast.
There are more features than you can point a stick at (seriously, it's
jam-packed). There are also report writers you can download, and others for
a small (ie ~$20) fee.
It really kicks butt. If it weren't for the bloody thing segfaulting every
so often, I'd be one happy camper. But anyway save often and all is
sweet!
By the way - I am not employed by Borland...
[reply]
[top]
[»]
Embedded databases?!
by j - Sep 30th 2001 04:20:03
None of the so qualified "embedded" database is an embedded database IMHO.
Also I don't understand why "database" always means "SQL databases" for so
many people.
BerkeleyDB and InnoDB *are* embedded databases. They work
very well without the bloat of MySQL and they provide transactions, an API
that works with tons of languages, etc.
Most people use SQL databases just for indexing data. They need the bloat
of an SQL database just to execute basic INSERT and SELECT statements. Why
don't they simply use GDBM, NDBM, DynDB, etc? These are very simple
key/data pairs indexing libraries. They only need a few kilobytes of
memory, the overhead of indexing is very low, and lookups are damn fast.
For constant databases, they are even faster and smaller alternatives like
CDB and PureDB.
Also, don't forget that *filesystems* are powerful databases. ReiserFS can
store millions of files in the same directory without any trouble, and with
fast lookups. By using simple files, you can easily store dynamic and
variable-size data.
I don't mean that SQL database suck. They provide some features that avoid
programmers to reinvent the wheel (like proper locking, sorting, merging,
and type checking) . But people tend to *need* them for everything, even
for very basic PHP code that really don't need the power of an SQL
database. An implication is that applications are running slower that
equivalents that were running 10 years ago on Atari ST. Plus it adds
security flaws : so many people forget to properly quote their SQL queries.
Or when they do, they often forget that users can forge cookies, host
names, etc. The lately discovered exploits in MySQL and PostgreSQL
authentication modules for Apache are just another example. This kind of
problem can't happen with embedded non-SQL databases. They can manage any
variable-length key, any variable-length data, binary or not, with simple
function calls, not a flawed query language.
--
Theoretically it is possible that all quantums in my body decide to tunnel through space-time at exactly the same time and with the exactly samedirection and speed vector for exactly the same duration of time and that I am suddenly effectively teleported to the surface of the moon.
[reply]
[top]
[»]
Access replacement
by iomud - Sep 30th 2001 02:07:08
Those who might think they need an access-like replacement for any
operating system that can run kde should look into rekall. It
looks more practical than access in that it's functions are geared towards
creating database applications, and it's extensible.
[reply]
[top]
[»]
Re: Access replacement
by Marco Pratesi - Sep 30th 2001 03:53:52
You can also consider Star Office with ODBC interfacing
to PostgreSQL or MySQL, as an example.
I use Star Office 5.2, UnixODBC, and PostgreSQL...
please explain me which features are lacking w.r.t. MS Access...
maybe it lacks support for Microsoft crashes and loss of data ? :)
[reply]
[top]
[»]
Re: Access replacement
by Sylwester Mikulski - Oct 1st 2001 15:22:39
Hello,
> I use Star Office 5.2, UnixODBC, and
> PostgreSQL...
> please explain me which features are
> lacking w.r.t. MS Access...
> maybe it lacks support for Microsoft
> crashes and loss of data ? :)
I made few databses in M$access, and now I try to find replacment for it.
And belive me, I can't find :(( sorry (mayby I'm looking to poor.), I like
linux, I love it as a servers, but access as a client is v.v.v. good peace
of software.
features ? (access 97 - good one)
1) easy to maitence tables, indexes, relations
2) very good forms, subforms, objects (combo, lists)
3) v.v.v. good reports (subreports, groups of data, headers, footers for
every goups - v. good)
4) v.v.v. easy vb & dao
5) v. easy to connect to sql servers (by odbc)
Belive me. I have eg one system with near 100 tables, 200 forms, 50
reports, many tons of code, 15 users online, few instances in smalest
branch and I working. In additional on mdb (few mdb) files. And pls, don't
say it loose data. 3 years withought any misses. sorry :((( Thats facts
:((( Another example ? Old access 2.0. 5 branch, few users, but mamy data
(35.000) in one table, (10.000-20.000) in next 3. In old access 2.0. 5
years withought any looses... Sorry, good database will work near perfect.
Now I just preparing to move from mdb to postgres & debian, but I can't
find replacment for client side. o.k. ? sorry, it is good peace of soft -
in my opinion. I'll try 'Rekall'.
And Star office... I was try to make some forms, but I don't know.... but
I don't like it :(( - it is too slow. And I don't know the language for SO
(withought internal language, you could create database for music CD in
Your home. But not for commercial use). Have You know some good client soft
for databse. I think about Delphi/Kylix, but I don't know.
br
slv
--
---
slv_news@wp_remove_.pl
[reply]
[top]
[»]
Re: Access replacement
by Marco Pratesi - Oct 2nd 2001 11:40:02
>
> (10.000-20.000) in next 3. In old access
> 2.0. 5 years withought any looses...
>
If you have never lost data cause to MS Access, it is *only luck*.
[reply]
[top]
[»]
Re: Access replacement
by mly - Feb 4th 2002 10:25:24
> Belive me. I have eg one system with
> near 100 tables, 200 forms, 50 reports,
> many tons of code, 15 users online,
I guess that is a reasonably sized system,
but with surprisingly few users. But I think
you are pushing the limits of Access there.
> Thats facts :((( Another example ? Old
> access 2.0. 5 branch, few users, but
> mamy data (35.000) in one table,
> (10.000-20.000) in next 3.
I guess that is a lot of data for Access,
but you should realize that there is a reason
that 4GB limits on files have been considered
a problem for years. In the last multi user
system I worked with, your figures above are
more or less how much the main tables grew
per week.
My personal impression of Access
is that it's dead easy to make very
simple things. I've made some simple
applications with just a few forms, but
as demands got higher, I've always
left it and shifted to other tools.
Partly this might be beacause I didn't make
the needed effort to learn it properly, but
there are some other reasons as well.
* I've gotten used to OO programming and I
feel that I benefit a lot from using OO.
* As projects grow I want to automate things,
generate code and database schemas,
analyze code with other programs and so on.
Then I want all my source to be plain text files.
* I believe in a layered approach, separating
user inteface, program logic and data
storage. Tools like Access doesn't help much
in this, and a large part of the features in
these tools can't be used if you consider it
a taboo for the GUI code to "know" how data
is stored etc.
* I also often find that it's good to be flexible.
(Thus my layered approach.) I might want
to use a GUI for data entry, but I might also
want to be able to enter data from the web
or from information extracted from emails or
fetched from the internet with a spider.
My typical approach has for several years been
to code as much as I can in Python. (So far it
has only been customer demands that have
stopped me from coding everything in Python.)
I build GUIs in wxPython and right now I use
ZODB for storage, but Python works smoothly
with most SQL databases as well.
For a system as big as 200 forms I am fairly
sure that you will get down in a development
time and maintenance cost of maybe 20%
compared to Access if you use a good object
oriented approach in Python.
For a very small application, and for users who
are not really programmers, I guess it's MUCH
more difficult to write wxPython GUI code than
to click and drag in Access.
But as systems grow you reach a point where
tools like Access are far from optimal. I've worked
with similar tools, and suddenly, you need to
change something that exists in 200 forms. Then
you need to manually point and click in 200 forms,
but you are likely to miss a few... With a smarter
approach this might instead be one change in one
class, or it might be a matter of writing a script
that will make the changes in all your program files.
[reply]
[top]
[»]
Re: Access replacement
by Owen Lloyd - Feb 6th 2002 06:37:47
good peace of software.
>
> features ? (access 97 - good one)
> 1) easy to maitence tables, indexes,
> relations
> 2) very good forms, subforms, objects
> (combo, lists)
> 3) v.v.v. good reports (subreports,
> groups of data, headers, footers for
> every goups - v. good)
> 4) v.v.v. easy vb & dao
> 5) v. easy to connect to sql servers
> (by odbc)
> Belive me. I have eg one system with
> near 100 tables, 200 forms, 50 reports,
>I think about Delphi/Kylix, but
> I don't know.
>
> br
> slv
>
A database should store data. Nothing more, nothing less. What/how you
choose to create the applications that reference, supply and alter the data
contained in the database is a *completley* seperate issue. As others have
pointed out - you can write your apps in anything from C, Java, PHP to
Kylix - it matters not - as long as they support some method of
communicating with the database engine (eg via SQL or ODBC). Acesss is not
a DBMS - there is no DBMS involved with access - the data is stored in a
file, and all the clients fight over the file. Its a great tool for
prototyping, and prodcuing a small scale system that will always *stay*
small - as long as you don't mind the clunky way it looks, and the huge
amounts of resources your apps take up on their host PCs. However - Access
apps just don't scale well - try your apps with 100 users all banging away
at the forms and it will soon begin to show signs of falling apart.
Almost everything I produce these days, I do as a HTML based front end
written in PHP (but it could just as easily be JSPs or even C at the back
end). The DBMS I use is MySQL but again, any reasonable one would do.
This means - no client software = no user fiddling = no client problems,
and no rollouts to do.
And if I want a really big app, I can reaplce MySQL with a heavyweight
DBMS with transaction support and features like bitmapped indexes, paralell
loading and so forth (like, say: Oracle), with only minor changes to the
app code - and no chnages at all on the client PCs.
Access is a toy from an earlier age when "doing it properly" was
out of reach of many small businesses. This is not the case these days, and
there's no excuse for it. You don't sound like you fall into this category
but it always seems to be used by people who have not grasped the first
principles of data design, normalisation or basic good coding practice.
They think they can operate it like Word - because it has buttons and looks
the same. This is a problem that permeates the Windows world - their
server OS has the same interface as their desktop offering, and so everyone
thinks they are systems architects or sys admins all of a sudden.
[reply]
[top]
[»]
Re: Access replacement
by stefan - Jun 8th 2004 07:53:03
I don't want an access replacement to occur on linux, and support most of
the comments from mly.
When I generate sql-statements, I do it with the keyboard. I can use these
and move them to a postgres, oracle or informix - database.
On access the sql-interface is a tool to punish users. It doesn't even
allow indentation to make your code structured and readable.
Having an graphical userinterface is nice, if you don't exceed 10 tables.
Every serious database has the possibility, to access it from the
commandline, which is needed for automated processes, invoked from scripts
of all kind - a sheduler for example.
And the performance is lousy.
And if you use it, you're stuck on it. The flypaper under the databases -
if you would call it a database.
Access is the typical ms-application: Fast take-off, fast crah. :)
[reply]
[top]
[»]
SAPdb embedded?
by Gerhard Häring - Sep 30th 2001 00:56:45
SAPdb certainly isn't an embedded database. It's a a relatively heavyweight
and complete database that runs standalone. For me, an embedded database is
on that can be linked in as a library, which isn't the case here.
[reply]
[top]
[»]
Re: SAPdb embedded?
by Gerhard Häring - Sep 30th 2001 00:58:26
> SAPdb certainly isn't an embedded
> [...]
Other than that minor pick, this certainly is a great article!
[reply]
[top]
[»]
Re: SAPdb embedded?
by PerlChild - Jan 25th 2004 21:19:51
> SAPdb certainly isn't an embedded
> database. It's a a relatively
> heavyweight and complete database that
> runs standalone. For me, an embedded
> database is on that can be linked in as
> a library, which isn't the case here.
Perhaps, but from the point of view where the database is optimized for a
particular application and/or is designed from the point of view of that
application, sapdb would be an embedded database made for SAP R/3 and
related solutions(despite the fact you can run SAP solutions on other
databases).
[reply]
[top]
|
|
