Re: [sqlite] drop table question ?
You may not have a column name; it might be a calculated column; for example, count(*) g -Original Message- From: John Machin [mailto:sjmac...@lexicon.net] Sent: Tuesday, February 03, 2009 2:05 PM To: General Discussion of SQLite Database Subject: Re: [sqlite] drop table question ? On 4/02/2009 12:37 AM, Brad Stiles wrote: >>> For my own edification, why the "order by 1" clause? >> To sort them in ascending order of table name, which might make >> old-fashioned capers like visual scrutiny a little easier. > > OK then, why would one not use the column name? Maybe because one is an obscurantist sometimes :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On 4/02/2009 12:37 AM, Brad Stiles wrote: >>> For my own edification, why the "order by 1" clause? >> To sort them in ascending order of table name, which might make >> old-fashioned capers like visual scrutiny a little easier. > > OK then, why would one not use the column name? Maybe because one is an obscurantist sometimes :-) ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On Tue, Feb 3, 2009 at 10:36 AM, Brad Stiles wrote: >>> >> For my own edification, why the "order by 1" clause? >>> > >>> > To sort them in ascending order of table name, which might make >>> > old-fashioned capers like visual scrutiny a little easier. >>> >>> OK then, why would one not use the column name? >> >> It does. > > No, I meant why not use the column name, instead of the number? > > Why use "order by 1" instead of "order by name"? > > I understand that "1" refers to the "'drop table ' || name || ';'" > result column, but why bother? It seems simpler to me to use the > actual column name. Is there a performance thing involved here, or > simply a preference? because the way the query was written, there was no alias provided to the selected column, so just giving the column number worked. Is there a performance difference? I don't know... just another way of doing things perhaps. > > /bs > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
>> >> For my own edification, why the "order by 1" clause? >> > >> > To sort them in ascending order of table name, which might make >> > old-fashioned capers like visual scrutiny a little easier. >> >> OK then, why would one not use the column name? > > It does. No, I meant why not use the column name, instead of the number? Why use "order by 1" instead of "order by name"? I understand that "1" refers to the "'drop table ' || name || ';'" result column, but why bother? It seems simpler to me to use the actual column name. Is there a performance thing involved here, or simply a preference? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On Tue, Feb 3, 2009 at 10:28 AM, Jay A. Kreibich wrote:
> On Tue, Feb 03, 2009 at 08:37:10AM -0500, Brad Stiles scratched on the wall:
>> >> For my own edification, why the "order by 1" clause?
>> >
>> > To sort them in ascending order of table name, which might make
>> > old-fashioned capers like visual scrutiny a little easier.
>>
>> OK then, why would one not use the column name?
>
> It does.
>
> The given statement (with a bit of editing) was:
>
> SELECT 'drop table ' || name || ';'
> FROM sqlite_master
> WHEREtype = 'table'
> ANDname GLOB 'X[0-9][0-9][0-9][0-9]'
> ORDER BY 1;
>
> You'll notice there are no commas between the SELECT and FROM... the
> SELECT statement only produces one column. That column consists of a
> series of strings in the format:
>
> 'drop table ;'
>
> Since each string starts with the exact same prefix ('drop table ')
> the end result is that it will sort by table name. And, because of
> the GLOB format, it will sort by the numeric value of the table name.
>
> -j
>
> --
> Jay A. Kreibich < J A Y @ K R E I B I.C H >
>
> "Our opponent is an alien starship packed with atomic bombs. We have
> a protractor." "I'll go home and see if I can scrounge up a ruler
> and a piece of string." --from Anathem by Neal Stephenson
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On Tue, Feb 03, 2009 at 08:37:10AM -0500, Brad Stiles scratched on the wall:
> >> For my own edification, why the "order by 1" clause?
> >
> > To sort them in ascending order of table name, which might make
> > old-fashioned capers like visual scrutiny a little easier.
>
> OK then, why would one not use the column name?
It does.
The given statement (with a bit of editing) was:
SELECT 'drop table ' || name || ';'
FROM sqlite_master
WHEREtype = 'table'
ANDname GLOB 'X[0-9][0-9][0-9][0-9]'
ORDER BY 1;
You'll notice there are no commas between the SELECT and FROM... the
SELECT statement only produces one column. That column consists of a
series of strings in the format:
'drop table ;'
Since each string starts with the exact same prefix ('drop table ')
the end result is that it will sort by table name. And, because of
the GLOB format, it will sort by the numeric value of the table name.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
Sorry for the redundant reply. It looks like it was already answered. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of Wilson, Ron P Sent: Tuesday, February 03, 2009 9:35 AM To: General Discussion of SQLite Database Subject: Re: [sqlite] drop table question ? You will have to do this with code. select name from sqlite_master where name like 'x%'; that will give you a list of all tables that start with x; you can then delete all tables with names in the result set. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of baxy77bax Sent: Tuesday, February 03, 2009 4:08 AM To: sqlite-users@sqlite.org Subject: [sqlite] drop table question ? hi simple question : How to drop all tables in my database that start , for example, with X? table 1 is X1998 table 2 is X8676 table 3 is X2912 ... thanx -- View this message in context: http://www.nabble.com/drop-table-question---tp21806118p21806118.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
You will have to do this with code. select name from sqlite_master where name like 'x%'; that will give you a list of all tables that start with x; you can then delete all tables with names in the result set. RW Ron Wilson, S/W Systems Engineer III, Tyco Electronics, 434.455.6453 -Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] On Behalf Of baxy77bax Sent: Tuesday, February 03, 2009 4:08 AM To: sqlite-users@sqlite.org Subject: [sqlite] drop table question ? hi simple question : How to drop all tables in my database that start , for example, with X? table 1 is X1998 table 2 is X8676 table 3 is X2912 ... thanx -- View this message in context: http://www.nabble.com/drop-table-question---tp21806118p21806118.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On Tue, Feb 3, 2009 at 8:37 AM, Brad Stiles wrote: >>> For my own edification, why the "order by 1" clause? from the docs... http://www.sqlite.org/lang_select.html Each term of an ORDER BY expression is processed as follows: If the ORDER BY expression is a constant integer K then the output is ordered by the K-th column of the result set. If the ORDER BY expression is an identifier and one of the output columns has an alias by the same name, then the output is ordered by the identified column. Otherwise, the ORDER BY expression is evaluated and the output is ordered by the value of that expression. >> >> To sort them in ascending order of table name, which might make >> old-fashioned capers like visual scrutiny a little easier. > > OK then, why would one not use the column name? > > /bs > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Puneet Kishor http://www.punkish.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
>> For my own edification, why the "order by 1" clause? > > To sort them in ascending order of table name, which might make > old-fashioned capers like visual scrutiny a little easier. OK then, why would one not use the column name? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On 4/02/2009 12:06 AM, Brad Stiles wrote: >> sqlite> select 'drop table ' || name || ';' from sqlite_master where >> type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1; > > For my own edification, why the "order by 1" clause? To sort them in ascending order of table name, which might make old-fashioned capers like visual scrutiny a little easier. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
> sqlite> select 'drop table ' || name || ';' from sqlite_master where > type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1; For my own edification, why the "order by 1" clause? /bs ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
On 3/02/2009 8:07 PM, baxy77bax wrote: > hi > > simple question : How to drop all tables in my database that start , for > example, with X? > > table 1 is X1998 > table 2 is X8676 > table 3 is X2912 > ... > Catch the output of this: sqlite> select 'drop table ' || name || ';' from sqlite_master where type = 'table' and name glob 'X[0-9][0-9][0-9][0-9]' order by 1; check it carefully feed it back in Note: GLOB lets you be more precise than LIKE. Precision when dropping tables is a Good Thing :-) HTH, John ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
i was thinking the same thing but then it hit me that maybe there is already an existing solution. :) thanx RB Smissaert wrote: > > Run a loop in the code of your application and drop the tables. I don't > think SQLite can do something like this. > > RBS > >> >> hi >> >> simple question : How to drop all tables in my database that start , for >> example, with X? >> >> table 1 is X1998 >> table 2 is X8676 >> table 3 is X2912 >> ... >> >> thanx >> -- >> View this message in context: >> http://www.nabble.com/drop-table-question---tp21806118p21806118.html >> Sent from the SQLite mailing list archive at Nabble.com. >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> >> > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > -- View this message in context: http://www.nabble.com/drop-table-question---tp21806118p21806378.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] drop table question ?
Run a loop in the code of your application and drop the tables. I don't think SQLite can do something like this. RBS > > hi > > simple question : How to drop all tables in my database that start , for > example, with X? > > table 1 is X1998 > table 2 is X8676 > table 3 is X2912 > ... > > thanx > -- > View this message in context: > http://www.nabble.com/drop-table-question---tp21806118p21806118.html > Sent from the SQLite mailing list archive at Nabble.com. > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] drop table question ?
hi simple question : How to drop all tables in my database that start , for example, with X? table 1 is X1998 table 2 is X8676 table 3 is X2912 ... thanx -- View this message in context: http://www.nabble.com/drop-table-question---tp21806118p21806118.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
