Database recommendations for Windows app

[Will McGugan]

Hi,

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

I have googled and found plenty of information on databases, its just
that I dont have enough experience with databases to know which one is
best for my task!


Thanks in advance,

Will McGugan

 

[Thomas Bartkus]

Hi,

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

I have googled and found plenty of information on databases, its just
that I dont have enough experience with databases to know which one is
best for my task!

If you are writing strictly for the MS Windows platform
And
If the database is running single user with a "locally stored database" on a
Windows workstation.
Then
The MS Access file based (.mdb) system is hard to argue with.
You wouldn't have to distribute the (rather expensive) Access application
since this is little more than a front for the underlying DAO/ADO database
libraries that are built into the warp and woof of MS Windows. Your Python
application can address the DAO or ADO directly as these will libraries will
be pre-installed and/or freely available for MS Windows. Fast, freely
available, no license restrictions, and no need for extra downloads for a
reasonably recent (Win2000, XP) operating system.

On the other hand, if operating system portability were a concern (as it
should be!), I might suggest MySQL.
A Python/MySQL application can jump between Windows to Linux (all flavors!)
to Unix to BSD without need to alter a single line of code.

You were writing a Python app, weren't you
Thomas Bartkus

 

[Magnus Lycka]

Hi,

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

I have googled and found plenty of information on databases, its just
that I dont have enough experience with databases to know which one is
best for my task!

Neither do we, considering that brief description.

Will there be multiple simultaneous users?
Multiple simultaneous writers?
Do you require proper transaction management?
(Or is it a pure read-only database, since you know
exactly how many records the largest table has?)
Do you need to make ad-hoc queries?
What will your code look like?
* Is it object-oriented?
* Are there a lot of fixed relations between objects?
Are there other requirements such as need for undo or
change history management?
Does the application work with small chunks of data at a time
(such as in a ticket ordering system) or with larger chunks
(such as in a web site CMS)?
What size is it? It's a big difference between 455,905 integers
and 455,905 mp3 files for instance...
"A number of tables" tells us very little, more than suggesting
that it's more than one... Ten and 200 makes a big difference.

 

[Magnus Lycka]

If you are writing strictly for the MS Windows platform
And
If the database is running single user with a "locally stored database" on a
Windows workstation.
Then
The MS Access file based (.mdb) system is hard to argue with.

I disagree. What does .mdb/jet without Access offer you that you
don't get from e.g. SQLite except vendor lock-in and horrible
deviations from the SQL standard? Ok, it does give you somewhat
stronger typing, which you might possibly want, but if that's
an issue, I'd suggest embedded firebird (if we want serverless).

I'm not entirely sure something SQLish is the way to go though.

Also, the best ODBC adaper for Python, mxODBC, isn't free. Last
time I used adodbapi, it was a bit buggy, and pure ADO or DAO
solutions don't follow the Python standard DB-API 2.

 

[=?ISO-8859-1?Q?Gregory_Pi=F1ero?=]

I always figured a problem with using MySQL was distribution. Would
you have to tell your users to install MySQL and then to leave the
service running? I've never found an easy way to embed MySQL into a
python app, and even if you could, would you then have to pay for it?

-Greg

 

[Dan]

Take a look at Firebird. It can be run in embedded mode. It might be
overkill for your needs though...

 

[Dave Cook]

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

http://pysqlite.org

Dave Cook

 

[Thomas Bartkus]

Hi,

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

I have googled and found plenty of information on databases, its just
that I dont have enough experience with databases to know which one is
best for my task!

If you are writing strictly for the MS Windows platform
And
If the database is running single user with a "locally stored database" on a
Windows workstation.
Then
The MS Access file based (.mdb) system is hard to argue with.
You wouldn't have to distribute the (rather expensive) Access application
since this is little more than a front for the underlying DAO/ADO database
libraries that are built into the warp and woof of MS Windows. Your Python
application can address the DAO or ADO directly as these will libraries will
be pre-installed and/or freely available for MS Windows. Fast, freely
available, no license restrictions, and no need for extra downloads for a
reasonably recent (Win2000, XP) operating system.

On the other hand, if operating system portability were a concern (as it
should be!), I might suggest MySQL.
A Python/MySQL application can jump between Windows to Linux (all flavors!)
to Unix to BSD without need to alter a single line of code.

You were writing a Python app, weren't you
Thomas Bartkus

 

[Dan]

If you are writing strictly for the MS Windows platform
And
If the database is running single user with a "locally stored database" on a
Windows workstation.
Then
The MS Access file based (.mdb) system is hard to argue with.
You wouldn't have to distribute the (rather expensive) Access application
since this is little more than a front for the underlying DAO/ADO database
libraries that are built into the warp and woof of MS Windows. Your Python
application can address the DAO or ADO directly as these will libraries will
be pre-installed and/or freely available for MS Windows. Fast, freely
available, no license restrictions, and no need for extra downloads for a
reasonably recent (Win2000, XP) operating system.

And then XP Autoupdate executes, some of those Access/MSDE libraries are
updated, and you app is broken.

 

[Magnus Lycka]

I always figured a problem with using MySQL was distribution. Would
you have to tell your users to install MySQL and then to leave the
service running? I've never found an easy way to embed MySQL into a
python app, and even if you could, would you then have to pay for it?

There are more reasons than that not to use MySQL...
See e.g. http://sql-info.de/mysql/gotchas.html

It seems a lot of the most badly missed features are
appearing in MySQL 5.0, but as features are added, I
suppose the claimed advantages in performance and
simplicity withers away, and these features can hardly
be considered very mature yet. (One should also note
that MySQL manuals have often claimed that features
it lacked were useless, or even dangerous, until
MySQL AB decided to implement them themselves...


Also, the GPL/Commercial licence is not a good thing
for commercial apps, particularly since this licence
applies even to client libs.

Recent PostgreSQL versions are stable, fast, and have
native Windows versions.

 

[Thomas Bartkus]

And then XP Autoupdate executes, some of those Access/MSDE libraries are
updated, and you app is broken.

Hasn't happened yet!

For the record - I wouldn't recommend automatic updates of any kind for a
Linux/MySQL system either. For precisely the same reasons.

Thomas Bartkus

 

[Cameron Laird]

.
.
.

And then XP Autoupdate executes, some of those Access/MSDE libraries are
updated, and you app is broken.

Are you saying that Python-based applications are particularly
vulnerable in this all-too-common scenario? If so, I'm not
getting it; why is the architecture described more fragile than
more traditional Windows-oriented development patterns? If not,
then, ... well then I truly don't get your point.

 

[Dave Cook]

Are you saying that Python-based applications are particularly
vulnerable in this all-too-common scenario? If so, I'm not
getting it; why is the architecture described more fragile than
more traditional Windows-oriented development patterns? If not,
then, ... well then I truly don't get your point.

Maybe the point is the downside of depending on installed DLLs rather than
shipping your own.

Dave Cook

 

[Dan]

Maybe the point is the downside of depending on installed DLLs rather than
shipping your own.

Dave Cook

Yes, DLL hell.

 

[Ray Cote]

Hi,

I'd like to write a windows app that accesses a locally stored database.
There are a number of tables, the largest of which has 455,905 records.

Can anyone recommend a database that runs on Windows, is fast /
efficient and can be shipped without restrictions or extra downloads?

I have googled and found plenty of information on databases, its just
that I dont have enough experience with databases to know which one is
best for my task!


Thanks in advance,

Will McGugan

We use Firebird for that.
<http://www.firebirdsql.org>
--Ray
--

Raymond Cote
Appropriate Solutions, Inc.
PO Box 458 ~ Peterborough, NH 03458-0458
Phone: 603.924.6079 ~ Fax: 603.924.8668
rgacote(at)AppropriateSolutions.com
www.AppropriateSolutions.com

 

[Cameron Laird]

Yes, DLL hell.

?

OK, I'm with you part of the way. Typical "Access" developers
are *always* involved with DLL hell, right? You're surely not
saying that Python worsens that frustration, are you?

 

[Will McGugan]

Thanks for the replies. I think I'm going to go with sqllite for now.

For the curious, Im writing an interface to a nutritional database. So
you can type in a foodstuff and it will tell you whats in it..


Will McGugan

 

[Peter Hansen]

Thanks for the replies. I think I'm going to go with sqllite for now.

Your list didn't mention a few things that might be critical.
Referential integrity? Type checking? SQLite currently supports
neither. Just make sure you check the list of supported features to see
that it really does what you need.

-Peter

 

[Dennis Lee Bieber]

The MS Access file based (.mdb) system is hard to argue with.
You wouldn't have to distribute the (rather expensive) Access application
since this is little more than a front for the underlying DAO/ADO database
libraries that are built into the warp and woof of MS Windows. Your Python

The RDBM engine, used by Access, has the name JET... so more
formally, it is the JET libraries that handle .mdb files. Newer versions
of Access also are designed to handle M$ SQL Server/MSDE as "Access
Projects".

On the other hand, if operating system portability were a concern (as it
should be!), I might suggest MySQL.
A Python/MySQL application can jump between Windows to Linux (all flavors!)
to Unix to BSD without need to alter a single line of code.

Firebird might be a contender... Or if someone ever released a
version of MySQLdb built against the embedded library (so no separate
server is needed -- I suspect distribution licenses get in the way of
that)

--

 

[Dennis Lee Bieber]

It seems a lot of the most badly missed features are
appearing in MySQL 5.0, but as features are added, I
suppose the claimed advantages in performance and
simplicity withers away, and these features can hardly
be considered very mature yet. (One should also note
that MySQL manuals have often claimed that features
it lacked were useless, or even dangerous, until
MySQL AB decided to implement them themselves...

Might depend on how much is purely new code, and how much might
be purloined from the former SAP-DB (now "MaxDB by MySQL"), since a lot
of those features probably existed in the latter.

--