Getting random row through SQLAlchemy
How do I select a(or some) random row(s) from a table using SQLAlchemy? ...
database
Priority of a query in MS SQL
Is there a way to tell MS SQL that a query is not too important and that it can (and should) take it's time? Likewise is there a way to tell MS SQL that it should give higher priority to a query? ...
What are the pros and cons of object databases?
There is a lot of information out there on object-relational mappers and how to best avoid impedance mismatch, all of which seem to be moot points if one were to use an object database. My question is why isn't this used more frequently? Is it because of performance reasons or because object databases cause your data to become propriet...
How to implement database engine independent paging?
Task: implement paging of database records suitable for different RDBMS. Method should work for mainstream engines - MSSQL2000+, Oracle, MySql, etc. Please don't post RDBMS specific solutions, I know how to implement this for most of the modern database engines. I'm looking for the universal solution. Only temporary tables based solutio...
Firebird database replication
I have reached the point where I've decided to replace my custom-built replication system with a system that has been built by someone else, mainly for reliability purposes. Can anyone recommend any replication system that is worth it? Is FiBRE any good? What I need might be a little away from a generic system, though. I have five depar...
What is a Covered Index?
I've just heard the term covered index in some database discussion - what does it mean? ...
ID-ing Deadlocks in a Thread using Firebird
Developer looking for best method to identify a deadlock on a specific transaction inside a specific thread. We are getting deadlock errors but these are very general in FB 2.0 Deadlocks happening and they are leading to breakdowns in the DB connection between client and the DB. We send live ( once a second) data to the DB. We open a ...
Surrogate vs. natural/business keys
Here we go again, the old argument still arises... Would we better have a business key as a primary key, or would we rather have a surrogate id (i.e. an SQL Server identity) with a unique constraint on the business key field? Please, provide examples or proof to support your theory. ...
How to upgrade database schema built with an ORM tool?
I'm looking for a general solution for upgrading database schema with ORM tools, like JPOX or Hibernate. How do you do it in your projects? The first solution that comes to my mind is to create my own mechanism for upgrading databases, with SQL scripts doing all the work. But in this case I'll have to remember about creating new scripts...
Copying databases to remote locations
Our EPOS system copies data by compressing the database into a zip file, and manually copying to each till, using shared directories. Each branched is liked to the main location, using VPN which can be problematic, but is required for the file sharing to work correctly. Since our database system currently does not support replication,...
MySQL: "lock wait timeout exceeded"
I am trying to delete several rows from a MySQL 5.0.45 database: delete from bundle_inclusions; The client works for a while and then returns the error: Lock wait timeout exceeded; try restarting transaction It's possible there is some uncommitted transaction out there that has a lock on this table, but I need this process to trump...
Anyone using Lisp for a MySQL-backended web app?
I keep hearing that Lisp is a really productive language, and I'm enjoying SICP. Still, I'm missing something useful that would let me replace PHP for server-side database interaction in web applications. Is there something like PHP's PDO library for Lisp or Arc or Scheme or one of the dialects? ...
Storing email messages in a database
What sort of database schema would you use to store email messages, with as much header information as practical/possible, into a database? Assume that they have been fed into a script from the MTA and parsed into the relevant headers/body/attachments. Would you store the message body whole in the database table, or split any MIME-part...
Best way to archive live MySQL database
We have a live MySQL database that is 99% INSERTs, around 100 per second. We want to archive the data each day so that we can run queries on it without affecting the main, live database. In addition, once the archive is completed, we want to clear the live database. What is the best way to do this without (if possible) locking INSERTs? ...
Getting data from an oracle database as a CSV file (or any other custom text format).
A sample perl script that connects to an oracle database, does a simple SELECT query, and spits the results to stdout in CSV format would be great. Python or any other language available in a typical unix distribution would be fine too. Note that I'm starting from scratch with nothing but a username/password for a remote Oracle datab...
Which is faster/best? SELECT * or SELECT column1, colum2, column3, etc.
I've heard that SELECT * is generally bad practice to use when writing SQL commands because it is more efficient to SELECT columns you specifically need. If I need to SELECT every column in a table, should I use SELECT * or SELECT column1, colum2, column3, etc. Does the efficiency really matter in this case? I'd think SELECT * would be...
How to write an SQL query to find out which logins have been granted which rights in Sql Server 2005?
I'm responsible for some test database servers. Historically, too many other poeple have access to them. They run on SQL Server 2005. I've been writing queries and wrapping them in scripts so I can run a regular audit of rights. Finding out which users had Administrator rights on the server itself was fine, as was finding out who had...
How to Audit Database Activity without Performance and Scalability Issues?
I have a need to do auditing all database activity regardless of whether it came from application or someone issuing some sql via other means. So the auditing must be done at the database level. The database in question is Oracle. I looked at doing it via Triggers and also via something called Fine Grained Auditing that Oracle provides. ...
What is the best way to implement soft deletion?
Working on a project at the moment and we have to implement soft deletion for the majority of users (user roles). We decided to add an "is_deleted='0'" field on each table in the database and set it to '1' if particular user roles hit a delete button on a specific record. For future maintenance now, each SELECT query will need to ensure...
SaaS database design - Multiple Databases? Split?
I've seen SaaS applications hosted in many different ways. Is it a good idea to split features and modules across multiple databases? For example, putting things like the User table on one DB and feature/app specific tables on another DB and perhaps other commonly shared tables in another DB? ...