Re: [sqlite] if possible point another table
On 4 Apr 2014, at 7:55am, Darren Duncan wrote: > Putting that aside, for any SQL DBMS that supports the PREPARE and EXECUTE > keywords, you can have a SQL string value that contains a SQL statement and > execute it, and you can build that string in other SQL from your table like > with any string manipulation. This is a standard way to do it, if not the > most elegant, it is simple and powerful. And represents a huge vulnerability if hackers can trick the application into executing their own string. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if possible point another table
On 2014-04-03, 11:08 PM, Scott Robison wrote: I would think this would make an excellent virtual table extension. On Apr 3, 2014 11:03 PM, "Darren Duncan" wrote: On 2014-04-03, 7:19 PM, Andy Goth wrote: I don't believe this can be done in pure SQL since table names are not values. That doesn't mean it can't be done, though you will have to put some of the logic in your program itself. I expect that in the future this limitation will no longer exist. There's no reason that table names can't be values in principle. -- Darren Duncan So here's a thing. For any SQL DBMSs that support the "information schema", table names and the like are already represented as values in tables, so whatever data type is used there, allowing it for user-defined tables is a first step to what you want. Putting that aside, for any SQL DBMS that supports the PREPARE and EXECUTE keywords, you can have a SQL string value that contains a SQL statement and execute it, and you can build that string in other SQL from your table like with any string manipulation. This is a standard way to do it, if not the most elegant, it is simple and powerful. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if possible point another table
I would think this would make an excellent virtual table extension. On Apr 3, 2014 11:03 PM, "Darren Duncan" wrote: > On 2014-04-03, 7:19 PM, Andy Goth wrote: > >> I don't believe this can be done in pure SQL since table names are not >> values. That doesn't mean it can't be done, though you will have to put >> some of the logic in your program itself. >> > > I expect that in the future this limitation will no longer exist. There's > no reason that table names can't be values in principle. -- Darren Duncan > > > ___ > 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] if possible point another table
On 2014-04-03, 7:19 PM, Andy Goth wrote: I don't believe this can be done in pure SQL since table names are not values. That doesn't mean it can't be done, though you will have to put some of the logic in your program itself. I expect that in the future this limitation will no longer exist. There's no reason that table names can't be values in principle. -- Darren Duncan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if possible point another table
refactoring the model using views might be an option too. On Thu, Apr 3, 2014 at 9:50 PM, mm.w <0xcafef...@gmail.com> wrote: > Hello, > > yep; else; it would require an "eval" or the support to sql scripting, as > none of this exists, it has to be done at the program level; in a regular > two queries run, nothing fancy or extraordinary. > > Best. > > > On Thu, Apr 3, 2014 at 9:13 PM, Andy Goth wrote: > >> On 4/3/2014 10:10 PM, Keith Medcalf wrote: >> >>> >>> select * >>> from k1 >>> union >>> select * >>> from k2; >>> >> >> My understanding of the question was, how to select from tables whose >> names are somehow computed or extracted from another table >> >> -- >> Andy Goth | >> >> ___ >> 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] if possible point another table
Hello, yep; else; it would require an "eval" or the support to sql scripting, as none of this exists, it has to be done at the program level; in a regular two queries run, nothing fancy or extraordinary. Best. On Thu, Apr 3, 2014 at 9:13 PM, Andy Goth wrote: > On 4/3/2014 10:10 PM, Keith Medcalf wrote: > >> >> select * >> from k1 >> union >> select * >> from k2; >> > > My understanding of the question was, how to select from tables whose > names are somehow computed or extracted from another table > > -- > Andy Goth | > > ___ > 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] if possible point another table
On 4/3/2014 10:10 PM, Keith Medcalf wrote: select * from k1 union select * from k2; My understanding of the question was, how to select from tables whose names are somehow computed or extracted from another table -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] if possible point another table
select * from k1 union select * from k2; >-Original Message- >From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- >boun...@sqlite.org] On Behalf Of YAN HONG YE >Sent: Thursday, 3 April, 2014 19:46 >To: sqlite-users@sqlite.org >Subject: [sqlite] if possible point another table > >I have a table named aa like this: >id pid namenotetablename >1 0 s12 bbc k1 >2 1 sss vac k2 > >another table named k1: >id pid namenote >11 1 f2 aaa >12 1 fs bbc > >another table named k2: >id pid namenote >31 2 f2 aaa >32 2 fs bbc > >my question is: in table aa, if possible k1 point to another table "k1"? >select tablename from aa; >I want result is: >id pid namenote >11 1 f2 aaa >12 1 fs bbc >31 2 f2 aaa >32 2 fs bbc > > >___ >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] if possible point another table
On 4/3/2014 8:46 PM, YAN HONG YE wrote: I have a table named aa like this: id pid namenotetablename 1 0 s12 bbc k1 2 1 sss vac k2 another table named k1: id pid namenote 11 1 f2 aaa 12 1 fs bbc another table named k2: id pid namenote 31 2 f2 aaa 32 2 fs bbc my question is: in table aa, if possible k1 point to another table "k1"? select tablename from aa; I want result is: id pid namenote 11 1 f2 aaa 12 1 fs bbc 31 2 f2 aaa 32 2 fs bbc I don't believe this can be done in pure SQL since table names are not values. That doesn't mean it can't be done, though you will have to put some of the logic in your program itself. Here's a Tcl implementation that gives the same result as in your question. The actual work is done in the last two lines; the rest is for displaying the result nicely and for creating the database. # Recent version of Tcl required for [lmap] and max(). package require Tcl 8.6 package require sqlite3 # Executes an SQL query and prints it in nicely formatted columns. proc query {db query} { $db eval $query out { if {![info exists widths]} { lappend grid $out(*) set widths [lmap column $out(*) {string length $column}] } lappend grid [set row [lmap column $out(*) {set out($column)}]] set widths [lmap width $widths value $row {expr { max($width, [string length $value]) }}] } set format %-[join $widths "s %-"]s set display {} foreach row $grid { lappend display [format $format {*}$row] } join $display \n } # Create the database. sqlite3 db :memory: db eval { CREATE TABLE aa (id, pid, name, note, tablename); INSERT INTO aa VALUES (1, 0, 's12', 'bbc', 'k1'), (2, 1, 'sss', 'vac', 'k2'); CREATE TABLE k1 (id, pid, name, note); INSERT INTO k1 VALUES (11, 1, 'f2', 'aaa'), (12, 1, 'fs', 'bbc'); CREATE TABLE k2 (id, pid, name, note); INSERT INTO k2 VALUES (31, 1, 'f2', 'aaa'), (32, 1, 'fs', 'bbc'); } # Get list of subqueries which need to be UNION'ed. set queries [db eval {SELECT 'SELECT * FROM ' || tablename FROM aa}] # Join subqueries with UNION ALL, then execute and display result. puts [query db [join $queries " UNION ALL "]] -- Andy Goth | ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] if possible point another table
I have a table named aa like this: id pid namenotetablename 1 0 s12 bbc k1 2 1 sss vac k2 another table named k1: id pid namenote 11 1 f2 aaa 12 1 fs bbc another table named k2: id pid namenote 31 2 f2 aaa 32 2 fs bbc my question is: in table aa, if possible k1 point to another table "k1"? select tablename from aa; I want result is: id pid namenote 11 1 f2 aaa 12 1 fs bbc 31 2 f2 aaa 32 2 fs bbc ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users