Tag: sql
Complex Restore of Postgres data where multiples rows are deleted from a single row.
Complex Restore of Postgres data where multiples rows are deleted from a single row.
We ran into an issue with a client database where an application deleted upwards of 360 K records. In this process we found we needed to restore a database from several hours ago but we were unable to do a full system restore, so here is the recepie for how to resolve
- restore a database gziped file to a ‘sandbox’ database
#gunzip -c database_back.1014|psql -Uuser database_restore_20150909 - alter the public schema to rename it
#echo ‘alter schema public rename to ‘restore_09122015’|psql -Uuser database_restore_20150909 - dump the database and load to the live db in the new schema
#pg_dump -Uuser database _restore_20150909|psql -Uuser livedb - create insert statements which will insert all missing records from the new schema into the live (public) schema
#awk ‘/tbl/{gsub(“tbl”,””); print “insert into tbl”$1″ select r.* from restore_09122015.tbl”$1″ r left join tbl”$1” n using(“$1″id) where n is null; “}’ tbllist.save | psql –U user livedb.
Note that this relies on your database being in the same consistent naming format tblshortname.shortnameid.
ColdFusion: SQL Server – Cannot use empty object or column names. Use a single space if necessary
ColdFusion: SQL Server – Cannot use empty object or column names. Use a single space if necessary
When upgrading from an OLD version of Coldfusion to a newer one. We would occassionally receive the message
Cannot use empty object or column names. Use a single space if necessary
This message seemed as though it came from an error sent back from the database but the database version did not change at all.
However CF changed their version of SQL Server driver as the progressed and made it more compliant. Basically they made it so that you could no longer use double quotes (“”) in order to enclose an empty string.
So the SQL query that may have worked before
select * from myusers where username <> “”
would need to become
select * from myusers where username <> ”
PHP to reset all primary key sequences in your postgresql database
PHP to reset all primary key sequences in your postgresql database
Use the following php code t reset all of the primary key sequences with the max(id) currently in the db.
We use wrapper functions db_query (which returns an array from the db when a select statement is run) and db_exec() which runs an update or insert command against the db.
[code language=”php”]$sql = "SELECT t.relname as related_table,
a.attname as related_column,
s.relname as sequence_name
FROM pg_class s
JOIN pg_depend d ON d.objid = s.oid
JOIN pg_class t ON d.objid = s.oid AND d.refobjid = t.oid
JOIN pg_attribute a ON (d.refobjid, d.refobjsubid) = (a.attrelid, a.attnum)
JOIN pg_namespace n ON n.oid = s.relnamespace
WHERE s.relkind = ‘S’
AND n.nspname = ‘public’";
$qry = db_query($sql);
foreach($qry as $row)
{
$outsql = "select setval(‘$row[sequence_name]’,(select max($row[related_column]) from $row[related_table]))";
db_exec($outsql);
}[/code]
Great SQL Formatting Tool
Great SQL Formatting Tool
We often deal with very complex, dynamically generated SQL Statements which run from our applications.
If we need to debug them for any reason we often have to display them to the screen and then copy and paste them in to an SQL Query window. The problem is that those SQL Statements are not always formatted to be very readable. Sometimes they might even be on a single line. This requires a bunch of time going through and reformatting the sql statement, making it legible for debugging.
I have used this tool SQLinFORM several times in the past but I keep forgetting about when I dont have to use it very often.
http://www.sqlinform.com/
I just copy and paste the SQL into the window and click Format.
It does a great job formatting code quickly and even has some options for how you would like to see the output, I then select the output and paste it into my SQL Query window.
If you use it often they do have a version for sale.