> On Aug 29, 2015, at 11:28 PM, Simon Slavin <slavins at bigfraud.org> wrote:
>
> Someone else wrote some code which performed various queries and created
> real TABLEs with the appropriate data in.
Yes, something along these lines:
[[
attach database 'information_schema.db' as information_schema
]],
[[
pragma information_schema.automatic_index = off
]],
[[
pragma information_schema.encoding = 'utf-8'
]],
[[
pragma information_schema.foreign_keys = off
]],
[[
pragma information_schema.journal_mode = off
]],
[[
pragma information_schema.legacy_file_format = off
]],
[[
pragma information_schema.locking_mode = exclusive
]],
[[
pragma information_schema.synchronous = off
]],
[[
pragma information_schema.temp_store = memory
]],
[[
drop table if exists information_schema.catalog_name
]],
[[
create table if not exists information_schema.catalog_name
(
catalog_name text not null default 'main',
constraint catalog_name_pk
primary key (
catalog_name
)
)
]],
[[
insert
into information_schema.catalog_name
(
catalog_name
)
values (
'main'
)
]],
[[
drop table if exists information_schema.schemata
]],
[[
create table if not exists information_schema.schemata
(
catalog_name text not null default 'main',
schema_name text not null,
constraint schemata_pk
primary key (
catalog_name,
schema_name
),
constraint schemata_catalog_name_fk
foreign key (
catalog_name
)
references catalog_name
(
catalog_name
)
)
]],
[[
drop table if exists information_schema.tables
]],
[[
create table if not exists information_schema.tables
(
catalog_name text not null default 'main',
schema_name text not null,
table_name text not null,
table_type text not null,
constraint tables_pk
primary key (
catalog_name,
schema_name,
table_name
),
constraint tables_schemata_fk
foreign key (
catalog_name,
schema_name
)
references schemata
(
catalog_name,
schema_name
)
)
]],
[[
drop table if exists information_schema.columns
]],
[[
create table if not exists information_schema.columns
(
catalog_name text not null default 'main',
schema_name text not null,
table_name text not null,
column_name text not null,
ordinal_position integer not null,
column_default text,
is_nullable text not null,
data_type text not null,
constraint columns_pk
primary key (
catalog_name,
schema_name,
table_name,
column_name
),
constraint columns_uk
unique (
catalog_name,
schema_name,
table_name,
ordinal_position
),
constraint columns_tables_fk
foreign key (
catalog_name,
schema_name,
table_name
)
references tables
(
catalog_name,
schema_name,
table_name
)
)
]],
[[
drop table if exists information_schema.table_constraints
]],
[[
create table if not exists information_schema.table_constraints
(
constraint_catalog text not null default 'main',
constraint_schema text not null,
constraint_name text not null,
catalog_name text not null default 'main',
schema_name text not null,
table_name text not null,
constraint_type text not null,
constraint table_constraints_pk
primary key (
constraint_catalog,
constraint_schema,
constraint_name
),
constraint table_constraints_tables_fk
foreign key (
catalog_name,
schema_name,
table_name
)
references tables
(
catalog_name,
schema_name,
table_name
)
)
]],
[[
drop table if exists information_schema.referential_constraints
]],
[[
create table if not exists information_schema.referential_constraints
(
constraint_catalog text not null default 'main',
constraint_schema text not null,
constraint_name text not null,
unique_constraint_catalog text not null default 'main',
unique_constraint_schema text not null,
unique_constraint_name text not null,
match_option text not null,
update_rule text not null,
delete_rule text not null,
constraint referential_constraints_pk
primary key (
constraint_catalog,
constraint_schema,
constraint_name
),
constraint
referential_constraints_table_constraints_fk
foreign key (
constraint_catalog,
constraint_schema,
constraint_name
)
references table_constraints
(
constraint_catalog,
constraint_schema,
constraint_name
),
constraint
referential_constraints_unique_table_constraints_fk
foreign key (
unique_constraint_catalog,
unique_constraint_schema,
unique_constraint_name
)
references table_constraints
(
constraint_catalog,
constraint_schema,
constraint_name
)
)
]],
[[
drop table if exists information_schema.key_column_usage
]],
[[
create table if not exists information_schema.key_column_usage
(
constraint_catalog text not null default 'main',
constraint_schema text not null,
constraint_name text not null,
catalog_name text not null default 'main',
schema_name text not null,
table_name text not null,
column_name text not null,
ordinal_position integer not null,
constraint key_column_usage_pk
primary key (
constraint_catalog,
constraint_schema,
constraint_name,
column_name
),
constraint key_column_usage_uk
unique (
constraint_catalog,
constraint_schema,
constraint_name,
ordinal_position
),
constraint key_column_usage_table_constraints_fk
foreign key (
constraint_catalog,
constraint_schema,
constraint_name
)
references table_constraints
(
constraint_catalog,
constraint_schema,
constraint_name
),
constraint key_column_usage_columns_fk
foreign key (
catalog_name,
schema_name,
table_name,
column_name
)
references columns
(
catalog_name,
schema_name,
table_name,
column_name
)
)
]],
BeginTransaction =
[[
begin transaction
]],
EndTransaction =
[[
end transaction
]],
SelectSchemataSource =
[[
pragma database_list
]],
InsertSchemata =
[[
insert
into information_schema.schemata
(
schema_name
)
values (
lower( trim( %s ) )
)
]],
SelectSchemata =
[[
select schemata.schema_name as schema_name
from information_schema.schemata
where schemata.schema_name != 'temp'
order by schemata.schema_name
]],
SelectTableSource =
[[
select '{schema_name}' as schema_name,
sqlite_master.name as table_name,
case sqlite_master.type
when 'table' then 'base table'
else sqlite_master.type
end as table_type
from {schema_name}.sqlite_master
where sqlite_master.type in ( 'table', 'view' )
order by schema_name,
table_name
]],
SelectTempTableSource =
[[
select 'temp' as schema_name,
sqlite_temp_master.name as table_name,
case sqlite_temp_master.type
when 'table' then 'base table'
else sqlite_temp_master.type
end as table_type
from sqlite_temp_master
where sqlite_temp_master.type in ( 'table', 'view' )
order by schema_name,
table_name
]],
InsertTable =
[[
insert
into information_schema.tables
(
schema_name,
table_name,
table_type
)
values (
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) )
)
]],
SelectTable =
[[
select tables.schema_name as schema_name,
tables.table_name as table_name
from information_schema.tables
order by tables.schema_name,
tables.table_name
]],
SelectColumnSource =
[[
pragma {schema_name}.table_info( %s )
]],
InsertColumn =
[[
insert
into information_schema.columns
(
schema_name,
table_name,
column_name,
ordinal_position,
column_default,
is_nullable,
data_type
)
values (
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
%s,
%s,
lower( trim( %s ) ),
lower( trim( %s ) )
)
]],
SelectIndexSource =
[[
pragma {schema_name}.index_list( %s )
]],
SelectIndexColumnSource =
[[
pragma {schema_name}.index_info( %s )
]],
SelectForeignKeySource =
[[
pragma {schema_name}.foreign_key_list( %s )
]],
InsertKeyColumnUsage =
[[
insert
into information_schema.key_column_usage
(
constraint_schema,
constraint_name,
schema_name,
table_name,
column_name,
ordinal_position
)
values (
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
%s
)
]],
InsertTableConstraint =
[[
insert
into information_schema.table_constraints
(
constraint_catalog,
constraint_schema,
constraint_name,
catalog_name,
schema_name,
table_name,
constraint_type
)
select distinct
key_column_usage.constraint_catalog as constraint_catalog,
key_column_usage.constraint_schema as constraint_schema,
key_column_usage.constraint_name as constraint_name,
key_column_usage.catalog_name as catalog_name,
key_column_usage.schema_name as schema_name,
key_column_usage.table_name as table_name,
case
when key_column_usage.constraint_name like '%\_fk' escape
'\' then 'foreign key'
when key_column_usage.constraint_name like '%\_pk' escape
'\' then 'primary key'
when key_column_usage.constraint_name like '%\_uk' escape
'\' then 'unique'
end as constraint_type
from information_schema.key_column_usage
order by key_column_usage.catalog_name,
key_column_usage.schema_name,
key_column_usage.table_name,
key_column_usage.constraint_catalog,
key_column_usage.constraint_schema,
constraint_type,
key_column_usage.constraint_name
]],
DropForeignKeyExtract =
[[
drop table if exists foreign_key_extract
]],
CreateForeignKeyExtract =
[[
create temporary table if not exists foreign_key_extract
(
schema_name text not null,
table_name text not null,
id integer not null,
seq integer not null,
to_table text not null,
from_column text not null,
to_column text not null,
on_update text not null,
on_delete text not null,
match text not null
)
]],
InsertForeignKeyExtract =
[[
insert
into temp.foreign_key_extract
(
schema_name,
table_name,
id,
seq,
to_table,
from_column,
to_column,
on_update,
on_delete,
match
)
values (
lower( trim( %s ) ),
lower( trim( %s ) ),
%s,
%s,
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) ),
lower( trim( %s ) )
)
]],
DropForeignKeyExtractAggregate =
[[
drop view if exists foreign_key_extract_aggregate
]],
CreateForeignKeyExtractAggregate =
[[
create temporary view if not exists foreign_key_extract_aggregate as
select foreign_key_extract.schema_name as schema_name,
foreign_key_extract.table_name as table_name,
group_concat( distinct from_columns.column_name ) as
from_column,
foreign_key_extract.to_table as to_table,
group_concat( distinct to_columns.column_name ) as to_column,
max( on_update ) as on_update,
max( on_delete ) as on_delete,
max( match ) as match
from temp.foreign_key_extract
join information_schema.columns
as from_columns
on from_columns.schema_name = foreign_key_extract.schema_name
and from_columns.table_name = foreign_key_extract.table_name
and from_columns.column_name = foreign_key_extract.from_column
join information_schema.columns
as to_columns
on to_columns.schema_name = foreign_key_extract.schema_name
and to_columns.table_name = foreign_key_extract.to_table
and to_columns.column_name = foreign_key_extract.to_column
group by foreign_key_extract.schema_name,
foreign_key_extract.table_name,
foreign_key_extract.id,
foreign_key_extract.to_table
]],
DropTableConstraintAggregate =
[[
drop view if exists table_constraints_aggregate
]],
CreateTableConstraintAggregate =
[[
create temporary view if not exists table_constraints_aggregate as
select table_constraints.constraint_schema,
table_constraints.constraint_name,
table_constraints.constraint_type,
table_constraints.schema_name,
table_constraints.table_name,
group_concat( distinct columns.column_name ) as
constraint_column
from information_schema.table_constraints
join information_schema.key_column_usage
on key_column_usage.constraint_catalog =
table_constraints.constraint_catalog
and key_column_usage.constraint_schema =
table_constraints.constraint_schema
and key_column_usage.constraint_name =
table_constraints.constraint_name
join information_schema.columns
on columns.catalog_name = key_column_usage.catalog_name
and columns.table_name = key_column_usage.table_name
and columns.column_name = key_column_usage.column_name
where table_constraints.constraint_type in ( 'foreign key', 'primary
key', 'unique' )
group by table_constraints.constraint_schema,
table_constraints.constraint_name,
table_constraints.constraint_type,
table_constraints.schema_name,
table_constraints.table_name
]],
InsertReferentialConstraint =
[[
insert
into information_schema.referential_constraints
(
constraint_schema,
constraint_name,
unique_constraint_schema,
unique_constraint_name,
match_option,
update_rule,
delete_rule
)
select foreign_key_constraints.constraint_schema as constraint_schema,
foreign_key_constraints.constraint_name as constraint_name,
coalesce( primary_key_constraints.constraint_schema,
unique_key_constraints.constraint_schema ) as unique_constraint_schema,
coalesce( primary_key_constraints.constraint_name,
unique_key_constraints.constraint_name ) as unique_constraint_name,
foreign_key_extract.match as match_option,
foreign_key_extract.on_update as update_rule,
foreign_key_extract.on_delete as delete_rule
from temp.foreign_key_extract_aggregate as foreign_key_extract
left join temp.table_constraints_aggregate
as foreign_key_constraints
on foreign_key_constraints.schema_name =
foreign_key_extract.schema_name
and foreign_key_constraints.table_name =
foreign_key_extract.table_name
and foreign_key_constraints.constraint_column =
foreign_key_extract.from_column
and foreign_key_constraints.constraint_type = 'foreign key'
left join temp.table_constraints_aggregate
as primary_key_constraints
on primary_key_constraints.schema_name =
foreign_key_extract.schema_name
and primary_key_constraints.table_name =
foreign_key_extract.to_table
and primary_key_constraints.constraint_column =
foreign_key_extract.to_column
and primary_key_constraints.constraint_type = 'primary key'
left join temp.table_constraints_aggregate
as unique_key_constraints
on unique_key_constraints.schema_name =
foreign_key_extract.schema_name
and unique_key_constraints.table_name = foreign_key_extract.to_table
and unique_key_constraints.constraint_column =
foreign_key_extract.to_column
and unique_key_constraints.constraint_type = 'unique'
order by foreign_key_constraints.constraint_schema,
foreign_key_constraints.constraint_name,
unique_constraint_schema,
unique_constraint_name
]],
