Re: [sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Shane Dev
ange after the dependant view was created. it appears this is impossible using only SQL On Mon, 1 Apr 2019 at 02:38, Keith Medcalf wrote: > > On Sunday, 31 March, 2019 14:07, Shane Dev wrote: > > >Is it possible to create a view which switches rows and columns of a > >dynam

[sqlite] Is it possible to transpose a table using SQL?

2019-03-31 Thread Shane Dev
Hello, Is it possible to create a view which switches rows and columns of a dynamically changing table? For example, imagine we have table t1 where both columns and rows could change after the view has been created sqlite> select * from t1; Product/Region|Belgium|France|USA Oil_filter|1|2|3 Spar

[sqlite] Is it possible to concatenate an arbitrary number of columns into one?

2018-11-20 Thread Shane Dev
Hello, An asterisk in the result-column represents all columns from the FROM clause without explicitly naming them, https://www.sqlite.org/syntax/result-column.html Is there an SQL statement to concatenate all columns into a single column without explicitly naming them? If it existed, I could imag

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

2018-01-20 Thread Shane Dev
The following statement executes the random() function twice - sqlite> select random() union all select random(); random() 2678358683566407062 -5528866137931448843 sqlite> explain select random() union all select random(); addr opcode p1p2p3p4 p5 comment ---

Re: [sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

2018-01-18 Thread Shane Dev
s supposed to be enforced on all PRIMARY > KEY columns of _every_ table according to the SQL standard, but an early > version of sqlite included a bug which allowed NULLs and as a result sqlite > does not enforce this for ROWID tables. > > -Rowan > > On 19 January 2018 at

[sqlite] "Error: NOT NULL constraint failed ..." when no such constraint exists

2018-01-18 Thread Shane Dev
Hello, The following SQL works as I expect - sqlite> CREATE TABLE edges(parent int, child int, primary key(parent, child)); sqlite> insert into edges select null, 1; sqlite> select * from edges; parent child 1 sqlite> but if I remove the superfluous rowid column from the table definitio

Re: [sqlite] Defect: Redundant CTE table materialization gives wrong answer.

2018-01-18 Thread Shane Dev
Good question On 19 January 2018 at 06:04, petern wrote: > WITH flips(s) AS (VALUES (random()>0), (random()>0), (random()>0)) > SELECT sum(s),(SELECT sum(s) FROM flips) FROM flips; > sum(s),"(SELECT sum(s) FROM flips)" > 1,3 > --Expected output is 1,1. > > Why isn't the constant notional table t

Re: [sqlite] Can a record count column be added to this random hierarchical view?

2018-01-18 Thread Shane Dev
On 19 January 2018 at 05:41, petern wrote: > Were you expecting random() to return the same sequence when the view > materialized again in the subquery? > I was hoping to find a way to force the query planner to evaluate v_random_hierarchy only once. Perhaps this is not possible since it uses th

[sqlite] Can a record count column be added to this random hierarchical view?

2018-01-18 Thread Shane Dev
Hello, Here is a view which assigns randomly chosen parents to a sequence of children - CREATE VIEW v_random_hierarchy as with r(parent, child) as (select null, 1 union all select abs(random())%child+1, child+1 from r) select * from r limit 5; sqlite> select * from v_random_hierarchy; parent ch

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-17 Thread Shane Dev
> Any practical realtime video game using SQLite is probably > doing so only to save and restore the game board between games. and perhaps calculating the initial "maze" or other non time sensitive data processing > Even a cursory look into production > quality video game development will tell

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-17 Thread Shane Dev
y > wondering about. > > What is your background? Have you done production quality software > development work before? > > Is your application worthwhile? If you can say, what does your application > do for the end user that they couldn't do without it? > > > >

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-17 Thread Shane Dev
On 17 January 2018 at 08:45, petern wrote: > Shane. Expect to do a lot of hacking on shell.c. It's not intended as a > library but as the main program of a console application. That's a shame. I try very hard not to reinvent the wheel especially when the wheel question (shell.c) is widely used

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-16 Thread Shane Dev
function do_meta_command(char *zLine, ShellState *p). To those familiar with shell.c, is this a reasonable approach? On 17 January 2018 at 00:15, Richard Hipp wrote: > On 1/16/18, Shane Dev wrote: > > I tried - > > > > sqlite> CREATE VIRTUAL TABLE temp.t1 USING csv(filename=&#

Re: [sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-16 Thread Shane Dev
hout > necessity of the SQLite shell: > > https://sqlite.org/csv.html > > On Tue, Jan 16, 2018 at 12:47 AM, Shane Dev wrote: > > > Hi, > > > > I am looking for an efficient way to write a c program which performs the > > same function as the SQLite shell

[sqlite] c program which performs the same function as the SQLite shell command ".import"

2018-01-16 Thread Shane Dev
Hi, I am looking for an efficient way to write a c program which performs the same function as the SQLite shell command ".import" My initial strategy is to include the sqlite library source files and copy the control block from shell.c that begins after if( c=='i' && strncmp(azArg[0], "import",

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-15 Thread Shane Dev
Interesting. SQLite is written in ANSI C. Objective-C is a strict superset of ANSI C. Objective-C can be used to write software for OS X and iOS. Did the Apple engineers tell you why it is not possible to compile and run the SQLite shell on iOS? On 15 January 2018 at 02:16, Richard Hipp wrote:

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-15 Thread Shane Dev
Sorry, false alarm, the text is correct On 15 January 2018 at 13:36, Shane Dev wrote: > Hi Simon, > > .selftest looks interesting > > I think there is a typo in section 13 - > > The .selftest command reads the rows of the selftest table in selftest.tno > order. > &g

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-15 Thread Shane Dev
Hi Simon, .selftest looks interesting I think there is a typo in section 13 - The .selftest command reads the rows of the selftest table in selftest.tno order. On 15 January 2018 at 12:06, Simon Slavin wrote: > > > On 15 Jan 2018, at 10:08am, Shane Dev wrote: > > > Ideall

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-15 Thread Shane Dev
very interested to understand how that works. > > Peter > > > > > > On Sun, Jan 14, 2018 at 2:33 PM, Shane Dev wrote: > > > Hi Simon, > > > > I have found a way achieve this purely in the SQLite shell. The trick is > to > > make all rows in

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-14 Thread Shane Dev
;';"; sqlite> .once tcout1.sql sqlite> select * from tcout1; sqlite> .read tcout1.sql sqlite> select * from tcout2; .headers off .once tc1515968593 select * from tc; On 13 January 2018 at 19:57, Simon Slavin wrote: > On 13 Jan 2018, at 6:48pm, Shane Dev wrote: > &g

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-13 Thread Shane Dev
could be done in C/C++ on every target platform but I was hoping to avoid the complexities of the compiler toolchain and system programming languages at this stage. On 13 January 2018 at 21:09, Simon Slavin wrote: > > > On 13 Jan 2018, at 7:54pm, Shane Dev wrote: > > > What do you

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-13 Thread Shane Dev
xecution - or do I misunderstand your question? On 13 January 2018 at 20:40, Simon Slavin wrote: > > > On 13 Jan 2018, at 7:33pm, Shane Dev wrote: > > > I use mainly Linux (bash) and Windows (powershell) but my target > platforms > > also include Android, iOS, IoT (an

Re: [sqlite] Can an SQL script be built from within sqlite?

2018-01-13 Thread Shane Dev
Slavin wrote: > On 13 Jan 2018, at 6:48pm, Shane Dev wrote: > > > Is there a way to execute the contents of certain rows (the second row in > > this example) and replace it with its own result to create second table / > > view which could interpreted by the sqlite shell? &

[sqlite] Can an SQL script be built from within sqlite?

2018-01-13 Thread Shane Dev
Hello, I have a table of dot commands and SQL - sqlite> select sql from tcout1; sql .headers off select '.once tc'||strftime('%s','now'); --first execute this SQL statement and replace this line with its own result select * from tc; Is there a way to execute the contents of certain rows (the sec

Re: [sqlite] Is foreign key support disabled within triggers with raise functions?

2018-01-12 Thread Shane Dev
ABORT, which backs out the > change. > > Test 2 makes the change, then runs FAIL, which stops all further > processing. The FK constraints are never checked, and the changes are > not backed out. > > On 1/12/18, Shane Dev wrote: > > Hello, > > > > Perhaps i

Re: [sqlite] Is foreign key support disabled within triggers with raise functions?

2018-01-12 Thread Shane Dev
On 12 January 2018 at 00:48, Richard Hipp wrote: > On 1/11/18, Shane Dev wrote: > > > > CREATE VIEW vtrig as select 1; > > CREATE TRIGGER ttrig instead of insert on vtrig begin > > delete from deptab; > > delete from reftab; > > insert into deptab(r

[sqlite] Is foreign key support disabled within triggers with raise functions?

2018-01-11 Thread Shane Dev
Hello, Table deptab has a foreign key relationship with table reftab - sqlite> .sch CREATE TABLE reftab(id integer primary key); CREATE TABLE deptab(id integer primary key, ref int references reftab); foreign key support is enabled - sqlite> pragma foreign_keys; foreign_keys 1 the referenced t

Re: [sqlite] Is it possible to conditionally insert a record?

2018-01-10 Thread Shane Dev
Thanks, that works On 11 January 2018 at 06:40, Dan Kennedy wrote: > On 01/11/2018 03:41 AM, Shane Dev wrote: > >> Hi Dan, >> >> Your statement seems to insert a NULL into max_value >> > > So it does. How about this then: > > INSERT INTO max_value SE

Re: [sqlite] Is it possible to conditionally insert a record?

2018-01-10 Thread Shane Dev
There are are a few general patterns that, once > mastered, do explain what to expect most of the time. > > > > > > > > > > > > > > > > > On Wed, Jan 10, 2018 at 1:20 PM, Shane Dev wrote: > > > Hi Ryan, > > > > Nice! I ha

Re: [sqlite] Is it possible to conditionally insert a record?

2018-01-10 Thread Shane Dev
e NOT NULL); > > insert OR IGNORE into max_value select max(value) from source_table; > > select * from max_value; > > > -- Script Stats: Total Script Execution Time: 0d 00h 00m and > 00.031s > > > > On 2018/01/10 6:48 PM, Shane Dev wrote: > >>

Re: [sqlite] Is it possible to conditionally insert a record?

2018-01-10 Thread Shane Dev
ax(value) from source_table; max(value) sqlite> The behavior of SELECT max(X) from an empty table appears to contradict the documentation, or have I misunderstood something? On 10 January 2018 at 19:38, Dan Kennedy wrote: > On 01/10/2018 11:48 PM, Shane Dev wrote: > >> Hell

[sqlite] Is it possible to conditionally insert a record?

2018-01-10 Thread Shane Dev
Hello, sqlite> create table source_table(value); sqlite> create table max_value(max_value); sqlite> insert into max_value select max(value) from source_table; sqlite> select * from table_max_value; max_value sqlite> How can the maximum value of column source_table.value be inserted into max_val

[sqlite] Can error messages be concatenated in the raise function?

2018-01-10 Thread Shane Dev
Hello, From the documentation https://www.sqlite.org/syntax/raise-function.html, it is not clear to me if the 'error-message' must be a fixed string. Is there a way to raise a concatenated message? For example, a fix string error message - CREATE TABLE readonly(num int); CREATE TRIGGER ti_readon

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

2018-01-08 Thread Shane Dev
Thanks Donald. So simple in hindsight On 8 January 2018 at 23:20, Donald Griggs wrote: > > select random() > 0; -- random zero or one > > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/ma

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

2018-01-08 Thread Shane Dev
On 8 January 2018 at 21:58, Simon Slavin wrote: > > > num+1, cast(round(abs(random())/9223372036854775808) as int) from > > you’ve probably looking at sanity in the rear view mirror. Suppose > someone has to read your code and figure out what it’s meant to do. If you > expect your code t

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

2018-01-08 Thread Shane Dev
in preparing and binding the values than both generating and storing them with a single RCTE. On 8 January 2018 at 10:23, R Smith wrote: > > On 2018/01/08 11:17 AM, Shane Dev wrote: > >> >> P.S one reason I am using SQL instead the main application to perform such >> c

Re: [sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

2018-01-08 Thread Shane Dev
On 8 January 2018 at 09:19, petern wrote: > Your inner CTE will have to examine every generated row and count only > matches toward "running_num". > > Good idea, that works - sqlite> with r(num, rand, running_num) as (select 1, cast(round(abs(random())/9223372036854775808) as int), 1 union all s

[sqlite] How to add a running number column to a view which generates a limited series of randomly chosen integers?

2018-01-07 Thread Shane Dev
Hello, The view VRAND below generates a series of 3 randomly chosen integers - CREATE VIEW vrand as with r(num, rand) as ( select 1, cast(round(abs(random())/9223372036854775808) as int) union all select num+1, cast(round(abs(random())/9223372036854775808) as int) from r) select num from r where

Re: [sqlite] Efficient query to count number of leaves in a DAG.

2018-01-07 Thread Shane Dev
,0,0,EXECUTE LIST SUBQUERY 5 > --EQP-- 5,0,0,SCAN TABLE nodes > --EQP-- 5,1,1,SEARCH TABLE edges USING COVERING INDEX > sqlite_autoindex_edges_1 (parent=?) > --EQP-- 0,0,0,SCAN SUBQUERY 1 > parent|leafCount > 777|314 > Run Time: real 31.590 user 31.434202 sys 0.00 > >

Re: [sqlite] Efficient query to count number of leaves in a DAG.

2018-01-05 Thread Shane Dev
w on ideas. > > What're the record counts for nodes and edges? > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Shane Dev > Sent: Thursday, January 04, 2018 5:20 PM > To: SQLite mailing list > S

Re: [sqlite] Efficient query to count number of leaves in a DAG.

2018-01-04 Thread Shane Dev
r, but about 8 times slower than your query - sqlite> select * from leafcounts where parent=679; parent leafCount 679 2 Run Time: real 5.639 user 5.640625 sys 0.00 and that is without the reverseEdges index. I still don't understand why "leafcounts" is so much faster than &

Re: [sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread Shane Dev
* from cnt limit 3; On 3 January 2018 at 23:24, Shane Dev wrote: > Hi, > > This simple recursive common table expression returns all integers from 1 > to 3 as expected - > > sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt > limit 3) select * from cnt

[sqlite] Query optimizer and recursive common table expressions

2018-01-03 Thread Shane Dev
Hi, This simple recursive common table expression returns all integers from 1 to 3 as expected - sqlite> with recursive cnt(x) as (select 1 union all select x+1 from cnt limit 3) select * from cnt where x; x 1 2 3 sqlite> If the LIMIT constraint is moved from the compound SELECT to the subsequen

Re: [sqlite] Efficient query to count number of leaves in a DAG.

2018-01-02 Thread Shane Dev
om paths > where child in leaves > group by parent; > > > -Original Message- > From: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > On Behalf Of Shane Dev > Sent: Monday, January 01, 2018 11:14 AM > To: SQLite mailing list > Subject: [sqlite] Efficie

Re: [sqlite] Efficient query to count number of leaves in a DAG.

2018-01-01 Thread Shane Dev
ges(parent not null references nodes, child not null > >references nodes, primary key(parent, child)); > > Try your leaf counter again - after making the schema changes Clemens > suggested. > > Peter > > > On Mon, Jan 1, 2018 at 8:13 AM, Shane Dev wrote: > > > Hi, >

[sqlite] Efficient query to count number of leaves in a DAG.

2018-01-01 Thread Shane Dev
Hi, I want to the count the number of leaves (descendants without children) for each node in a DAG DAG definition - CREATE TABLE nodes(id integer primary key, description text); CREATE TABLE edges(parent not null references nodes, child not null references nodes, primary key(parent, child)); My

Re: [sqlite] Can select * from table where not exists (subquery) be optimized?

2018-01-01 Thread Shane Dev
, not how to compute it". Is this an exception to the rule where the query planner must be told how to compute the result? On 1 January 2018 at 10:58, Clemens Ladisch wrote: > Shane Dev wrote: > > CREATE TABLE nodes(id integer primary key, description text); > > CREATE TA

[sqlite] Can select * from table where not exists (subquery) be optimized?

2017-12-31 Thread Shane Dev
Hello, I have a directed acyclic graph defined as follows - sqlite> .sch CREATE TABLE nodes(id integer primary key, description text); CREATE TABLE edges(parent not null references nodes, child not null references nodes, primary key(parent, child)); Now I want to find the "roots" of the graph -

Re: [sqlite] How to prevent the insertion of cycles into a hierarchical table?

2017-12-24 Thread Shane Dev
Thanks for the wonderfully simple and concise solution. I see now triggers do support CTEs if they SELECT a RAISE() function. I never thought of using a BEFORE trigger. Fijne kerstdagen On 24 December 2017 at 17:17, E.Pasma wrote: > On 24/12/2017 11:56, Shane Dev wrote: > > Rela

[sqlite] How to prevent the insertion of cycles into a hierarchical table?

2017-12-24 Thread Shane Dev
Related to my previous question https://www.mail-archive.com/sqlite-users@mailinglists.sqlite.org/msg107527.html, I want to prevent the client from inserting a cycle. For example - sqlite> .sch edges CREATE TABLE edges(parent integer not null, child integer not null, constraint self_reference che

Re: [sqlite] How to detect cycles in a hierarchical table?

2017-12-21 Thread Shane Dev
this possibility. On 21 December 2017 at 12:11, Lifepillar wrote: > On 20/12/2017 22:31, Shane Dev wrote: > >> Hello, >> >> I have an edges table - >> >> sqlite> .sch edges >> CREATE TABLE edges(parent, child); >> >> sqlite> select *

[sqlite] Can a SELECT statement be used within a trigger?

2017-12-21 Thread Shane Dev
Hello The syntax diagram at the top of https://www.sqlite.org/lang_createtrigger.html implies a SELECT statement can be used between the BEGIN and END key words. For example - sqlite> CREATE TABLE stuff(thing text); sqlite> CREATE VIEW vstuff as select * from stuff; sqlite> CREATE TRIGGER tstuff

Re: [sqlite] How to detect cycles in a hierarchical table?

2017-12-20 Thread Shane Dev
elect x.parent, x.path || ' => ' || cast(edges.child as text), > edges.child from x > inner join edges on x.child = edges.parent > where x.path not like ('%' || cast(x.child as text) || ' => %')) > select * from x where parent = child; > > > Let me

[sqlite] How to detect cycles in a hierarchical table?

2017-12-20 Thread Shane Dev
Hello, I have an edges table - sqlite> .sch edges CREATE TABLE edges(parent, child); sqlite> select * from edges; parent child 1 2 1 3 2 4 3 1 4 5 5 2 Here we have two cycles - 1) 1 => 3 => 1 (length 1) 2) 2 => 4 => 5 => 2 (length 3) Cycles cause recursive

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

2017-12-20 Thread Shane Dev
uesday, 19 December, 2017 23:50 > >To: SQLite mailing list > >Subject: Re: [sqlite] Can we create a table where the value of one > >particular column <> another column? > > > > > > > >On 20 Dec 2017, at 6:30am, Shane Dev wrote: > > > >>

Re: [sqlite] Can we create a table where the value of one particular column <> another column?

2017-12-20 Thread Shane Dev
Nice solution! CREATE TABLE edges(parent references nodes, child references nodes check (parent<>child)); seems to be an equivalent but shorter statement. On 20 December 2017 at 07:49, Simon Slavin wrote: > > > Yes ! > > CREATE TABLE edges( > parent INTEGER references nodes, > child IN

[sqlite] Can we create a table where the value of one particular column <> another column?

2017-12-19 Thread Shane Dev
Let's say we have nodes and edges tables - sqlite> .sch nodes CREATE TABLE nodes(id integer primary key, description text); sqlite> .sch edges CREATE TABLE edges(parent references nodes, child references nodes); Can we restrict the edges table so that inserting or updating a row where edges.paren

Re: [sqlite] Can a trigger recursively update a table?

2017-12-14 Thread Shane Dev
brilliant! - it works - thanks On 14 December 2017 at 19:07, Clemens Ladisch wrote: > Shane Dev wrote: > > On 14 December 2017 at 12:59, Clemens Ladisch > wrote: > >> Shane Dev wrote: > >>> Can we conclude there is no single CTE or other SQL statement which

Re: [sqlite] Can a trigger recursively update a table?

2017-12-14 Thread Shane Dev
Hi Clemens, With your solution, how would you define the DELETE ON VHIERARCHY trigger? On 14 December 2017 at 12:59, Clemens Ladisch wrote: > Shane Dev wrote: > > Can we conclude there is no single CTE or other SQL statement which can > > update a branch of the tree starting

Re: [sqlite] Can a trigger recursively update a table?

2017-12-12 Thread Shane Dev
here id in (select id from cte); On 13 December 2017 at 00:59, J. King wrote: > CTEs cannot be used inside triggers for UPDATE statements. See near the > bottom of: > <http://sqlite.org/lang_createtrigger.html> > > On December 12, 2017 6:44:35 PM EST, Shane Dev > wrote: &

[sqlite] Can a trigger recursively update a table?

2017-12-12 Thread Shane Dev
Hi, I have a hierarchical table - sqlite> .sch hierarchy CREATE TABLE hierarchy(id integer primary key, parent references hierarchy, descrip text, status text); with some entries - sqlite> select * from hierarchy; id parent descrip status 1 rootopen 2 1 branc

Re: [sqlite] How do I combine these to 2 views ...

2017-11-26 Thread Shane Dev
So simple in hindsight, just add a second sort column 'close' to the union and then traverse the tree - thanks. On 26 November 2017 at 15:44, Clemens Ladisch wrote: > Shane Dev wrote: > > Any ideas to achieve this? > > Use another CTE to bring all rows into the correct

Re: [sqlite] How do I combine these to 2 views ...

2017-11-26 Thread Shane Dev
ciated. On 26 November 2017 at 11:30, Clemens Ladisch wrote: > Simon Slavin wrote: > > On 26 Nov 2017, at 8:02am, Shane Dev wrote: > >> Any ideas to achieve this? > > > > Use the UNION keyword to combine the results of the two SELECT commands > > That would

[sqlite] How do I combine these to 2 views ...

2017-11-26 Thread Shane Dev
Hello, I am try to combine the following 2 views - vtag and vparent_closetag sqlite> select id, level, line from vtag; id|lev|line id level line 1 0 2 1 3 1 4 2 5 1 6 2 7 3 8 2

Re: [sqlite] Can I recursively concatenate strings?

2017-11-23 Thread Shane Dev
> > > PS: If you do like the SQlite features and CTEs (which is one of my > favourite additions ever), I could post you the CTE example tutorials made > to accompany an sqlite DB manager (which I made very long ago, after the > introduction in 3.8 I think) - they have some nifty stuff, like splitti

Re: [sqlite] Can I recursively concatenate strings?

2017-11-23 Thread Shane Dev
Smith wrote: > > On 2017/11/22 11:56 PM, Shane Dev wrote: > >> Let's say I have a table of stringlengths - >> >> sqlite>select * from stringlengths; >> length >> 4 >> 1 >> 9 >> ... >> >> Can I create a view xstrings

[sqlite] Can I recursively concatenate strings?

2017-11-22 Thread Shane Dev
Let's say I have a table of stringlengths - sqlite>select * from stringlengths; length 4 1 9 ... Can I create a view xstrings containing strings (for example of char 'x') with the lengths specified in stringlengths? desired result - sqlite>select * from xstrings; string x ... P.S

Re: [sqlite] how into insert row into middle of table with integer primary key

2017-11-22 Thread Shane Dev
On 22 November 2017 at 17:08, Igor Korot wrote: > Hi, Shane, > > > What I don't understand is why do you need to do that? > Imagine I have a GUI element with a drop down list of fruit. The source of the list is my fruit table and it may have many entries. It might more convenient to list the pop

Re: [sqlite] how into insert row into middle of table with integer primary key

2017-11-21 Thread Shane Dev
Hi Igor, Homework exercise? No, this is purely a hobby project in my free time. My goal is see how much logic can moved from application code to the database. Why do I want store ID numbers whose values may change? Why not. Obviously, this would be bad idea if the ID column was referenced by othe

Re: [sqlite] how into insert row into middle of table with integer primary key

2017-11-20 Thread Shane Dev
fruit to recipes where needed has the fruit's primary keys shuffled, > the next day will see some really weird recipes when Banana ends up where > Pear was intended. Next you'll want to insert Watermelon... :) > > Cheers, > Ryan > > > On 2017/11/19 10:37 PM, Shane

[sqlite] how into insert row into middle of table with integer primary key

2017-11-20 Thread Shane Dev
Let's say I have a table of fruit - sqlite> .sch fruit CREATE TABLE fruit(id integer primary key, name text); with some entries - sqlite> select * from fruit; id|name 1|apple 2|pear 3|kiwi Is there an easy way to insert 'banana' between apple and pear while still maintaining a consistent order

[sqlite] Unexpected result from SELECT * FROM (subquery);

2017-11-05 Thread Shane Dev
Hello, In sqlite3, I executed the following statements - sqlite> select name from tabs where rowid=1; tab1 sqlite> select * from tab1; first rec sqlite> select * from (select name from tabs where rowid=1); tab1 I expected the last statement to evaluate the subquery first to be 'tab1' and then e

[sqlite] What is the most flexible way to exact the table name from a SQL statement

2017-10-28 Thread Shane Dev
Hello, Let's say I have a table containing of SQL statements, for example sqlite> .schema sql CREATE TABLE sql(statement text); sqlite> select * from sql; insert into tab1 select 'example text'; update tab2 set col2 = 123 where col2 = 1; delete from tab3 where col1 = 2; For the first row, I cou

Re: [sqlite] [SPAM] Fwd: How to correctly display unicode characters in Windows 10 / cmd.exe / sqlite3.exe?

2017-06-05 Thread Shane Dev
; select char(233); > char(233) > � > > sqlite> select unicode(char(233)); > unicode(char(233)) > 233 > > sqlite> select unicode('é');--input with alt+0233, crashes back to command > prompt > > > D:\> > > > -Original Message-

[sqlite] Fwd: How to correctly display unicode characters in Windows 10 / cmd.exe / sqlite3.exe?

2017-06-05 Thread Shane Dev
Hello, After logging in to Windows 10, I open a command prompt (cmd.exe) and change the code page to Unicode (UTF-8) >chcp 65001 Active code page: 65001 then I test this with a UTF-8 file - >type utf8test.txt néo66€ next I execute sqlite-tools-win32-x86-3190200\sqlite3.exe and check the encodi