sql

What are the viable database abstraction layers for Python

I'm starting to get involved in an open source project Gramps which is exploring switching their backend from BSDDB to a relational database. Either SQLite or MySQL we haven't fully decided and may even try to do both in some limited capacity. I'm a professional developer but I'm new to python so I'm not that familiar with the current se...

How can I find duplicate entries and delete the oldest ones in SQL?

I've got a table that has rows that are unique except for one value in one column (let's call it 'Name'). Another column is 'Date' which is the date it was added to the database. What I want to do is find the duplicate values in 'Name', and then delete the ones with the oldest dates in 'Date', leaving the most recent one. Seems like a ...

Large dataset (SQL to C#), long load time fix

I have a site I'm building, it's an application that creates mail merges (more or less...) based on a couple of user preferences. It can generate Cartesian joins worth of data without a problem, but in comes the needs of enterprise to make life a bit more difficult... I have to build the application so that, after verifying zip codes ...

How to make a sql search query more powerful?

I wrote this sql query to search in a table: Select * from TableName where Name Like '%spa%' The table contain these row for example: 1 Space Company. 2 Spa resort. 3 Spa hotel. 4 Spare Parts. 5 WithoutTheKeyword. I want to know how to edit this query so it return the results sorted like this: 2 Spa resort 3 Spa hotel 1 Space Compa...

CakePHP Tables without an 'id' field

How does CakePHP deal with tables that don't have an id column? I'm trying to save some relationship data, but Cake wants to "SELECT LAST_INSERT_ID()", however the table that it's trying to save to does not have the id column so the id is for a different table. Specifically, I've got tables for "Games" and "Players", and a relationship...

Table field that holds row count from another table

I have a field where I want to store the number of certain rows from another table. I have been incrementing this value when the rows are created, but feel this is probably not the best way. THen again I dont think that doing "count" on the table in question every time makes sense either. what is best approach? EDIT: Count is filtered ...

What does the "sys" prefix mean in the following?

select sys.database_name,sys.sysevent,sys.login_user from dual; what is sys in this query? Can I see the other functions that are in sys? What is the query for doing so? In what situations is this sys useful? Where will be the information about sys is stored in database? ...

order by case and alias

How can I use ORDER BY CASE @AccountOperator WHEN '>' THEN AccountTrxValue END ASC, CASE @AccountOperator WHEN '<' THEN AccountTrxValue END DESC when AccountTrxValue is an alias and a result of the following in the "select"? CASE WHEN PAA.RedeemValue>0 THEN PAA.RedeemValue * -1 ELSE PAA.EarnValue END AS AccountTrxValu...

SQL backlog calculation (MS Access)

Hi, I need to calculate the backlog from a table: components(ProductId, ComponentId, Status, StatusDate) where ComponentId, Status and StatusDate are the primary key. ProductId is a foreign key. Example: prod1, comp1, 01, 05/01/2009 prod1, comp1, 02, 05/01/2009 prod1, comp1, 03, 06/01/2009 prod1, comp1, 01, 07/01/2009 prod1, comp1, ...

SQL equality/inequality comparison with nullable values

first take, kludge solution, sentinel approach(it's imperative that your program should not allow inputting of sentinel value): select coalesce(a, -2147483648) = coalesce(b, -2147483648) as is_equal -- a little postgresism let's say you forgot to block the sentinel value on your program, the user inputted -2147483648 on the B field...

Linq to Sql and Non-PK, unique-FK relationship issues

Hello everyone, I've recently been reading Louis Davidson's book on Sql Server Database Design and found it quite informative. I've picked up on alot of concepts that I didn't previously know alot (or anything) about. Primarily - I picked up on a way to set up database relationships that I hand't tried before. Basically you use a surro...

MySQL: multiple grouping

So I have an example table called items with the following columns: item_id (int) person_id (int) item_name (varchar) item_type (varchar) - examples: "news", "event", "document" item_date (datetime) ...and a table person with the following columns: "person_id", "person_name". I was hoping to display a list of the top 2 submitters (+...

Convert a sql file to an embedded resource programatically

Does anyone know how to convert a .sql (scripts file) VS2008 to an embedded resource at runtime to be used in the Customs actions editor for install of database. ...

ORACLE PL/SQL: SELECT INTO variable, two statements, add variables

I have a variable called c_kilometers. I have a cursor that grabs a bunch of records that have those kilometers. I need to run two separate SELECT statements in the cursor that simply grab a kilometer from one table based on values in the cursor, and run another SELECT doing the same thing on another table. SELECT t.kilometers INTO c_ki...

SQL injection on INSERT

I have created a small survey web page on our company Intranet. This web page is not accessible from the outside. The form is simply a couple of radio buttons and a comments box. I would like to maintain good coding practices and would like to guard against SQL Injections. Can SQL injections happen on a insert statement with comments...

Do indexes suck in SQL?

Say I have a table with a large number of rows and one of the columns which I want to index can have one of 20 values. If I were to put an index on the column would it be large? If so, why? If I were to partition the data into the data into 20 tables, one for each value of the column, the index size would be trivial but the indexing ef...

Selecting elements that don't exist

Hi! I am working on an application that has to assign numeric codes to elements. This codes are not consecutives and my idea is not to insert them in the data base until have the related element, but i would like to find, in a sql matter, the not assigned codes and i dont know how to do it. Any ideas? Thanks!!! Edit 1 The table can b...

How do you handle multiple value types in MySQL when inheritance isn't supported?

I need to create a table of attributes where each record is essentially just a name-value pair. The problem is that the value can be a string, integer or decimal and I'm using MySQL which doesn't support table inheritance. So, the question is - should I create a separate table for each value type or should I just create str_value, int_va...

SQL create a temporary 'mapping' table in a select statement

I'm building up results by joining tables select t1.*, t2.col2 from t1, t2 where t1.col1=t2.col1 Is there a way to create a temporary 'mapping' table 'inline' in a select statement for instances where the t2 table doesn't exist? So something like select t1.*, tempt2.col2 from t1, (<create temp table>) tempt2 where ... I'm using O...

Semi-Distinct MySQL Query

I have a MySQL table called items that contains thousands of records. Each record has a user_id field and a created (datetime) field. Trying to put together a query to SELECT 25 rows, passing a string of user ids as a condition and sorted by created DESC. In some cases, there might be just a few user ids, while in other instances, ther...