[sqlite] i think this is a bug in sqlite? thanks
I was about the suggest the same thing that was suggest in that thread. Verify the types of those values. -Original Message- From: sqlite-users-bounces at mailinglists.sqlite.org [mailto:sqlite-users-boun...@mailinglists.sqlite.org] On Behalf Of Anthony Damico Sent: Thursday, December 10, 2015 10:56 AM To: sqlite-users at mailinglists.sqlite.org Subject: [sqlite] i think this is a bug in sqlite? thanks https://github.com/rstats-db/RSQLite/issues/125#event-488337588 ___ sqlite-users mailing list sqlite-users at mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users This email and any attachments are only for use by the intended recipient(s) and may contain legally privileged, confidential, proprietary or otherwise private information. Any unauthorized use, reproduction, dissemination, distribution or other disclosure of the contents of this e-mail or its attachments is strictly prohibited. If you have received this email in error, please notify the sender immediately and delete the original.
[sqlite] i think this is a bug in sqlite? thanks
https://github.com/rstats-db/RSQLite/issues/125#event-488337588
[sqlite] Thanks SQLite
On 2015-07-31 06:22 PM, Gabor Grothendieck wrote:
> Both the solutions transformed the correlated subquery into a join prior to
> forming the CTE. Can we conclude, in general, that CTEs do not support
> correlated subqueries?
Slow down please! - Your example did work and did get the correct result
using a sub-query AND a CTE. There is no problem with that.
I've simply shown a way to use the "A.avgSalary" notation which you
mentioned would be nice to have, together with a slight optimization
(which I saw mimicked the way John suggested - obviously, he too, moved
to optimize) - but none of those examples mean that sub-queries CANNOT
exist in (or with) CTE's. It simply means that in this specific query,
the CTE is better than the sub-query.
Also, the join is formed with the CTE, I am not sure why you use the
words "prior to forming the CTE".
I hope that is more clear...
Cheers!
Ryan
>
> On Fri, Jul 31, 2015 at 11:30 AM, R.Smith wrote:
>
>>
>> On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
>>
>>> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin
>>> wrote:
>>>
>>> I am noting an overlap with the things SQLite users have been using
sub-SELECTs for.
Here is a self contained example that can be fed into the sqlite3 command
>>> line tool. It uses WITH to factor out the subquery; however, the annoying
>>> part about it is that even though we have factored out the subquery we
>>> still have to include a simplified version of the subquery, viz.
>>> (select avgSalary from A)
>>> It would have been nice if we could just replace (select avgSalary from A)
>>> with (A.avgSalary) or even (A) but those substitutions do not work:
>>>
>> Well, you can, sort-of, if you use the CTE column naming. Consider this
>> simplified version of your Query (also using the CTE to do the actual
>> aggregate work once only):
>>
>> create table Emp (emp text, salary real, dept text);
>> insert into Emp values
>> ('a', 1, 'A'),
>> ('b', 2, 'A'),
>> ('c', 3, 'A'),
>> ('d', 1, 'B'),
>> ('e', 2, 'B'),
>> ('f', 3, 'B');
>>
>>
>> WITH A(dept,avgSalary) as (
>> SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
>> )
>> SELECT Emp.*
>>FROM Emp, A
>> WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
>>
>>
>>-- emp | salary | dept
>>-- - | -- | -
>>-- c | 3.0 | A
>>-- f | 3.0 | B
>>
>>
>> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
>>> create table Emp (emp text, salary real, dept text);
>>> insert into Emp values ('a', 1, 'A');
>>> insert into Emp values ('b', 2, 'A');
>>> insert into Emp values ('c', 3, 'A');
>>> insert into Emp values ('d', 1, 'B');
>>> insert into Emp values ('e', 2, 'B');
>>> insert into Emp values ('f', 3, 'B');
>>> WITH A as
>>>(SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
>>>SELECT *
>>>FROM Emp AS e1
>>>WHERE salary > (select avgSalary from A);
>>>
>>> Here is a sample run assuming the above has been placed into a file named
>>> test.sqlite.
>>>
>>> C:\> sqlite3 < test.sqlite
>>> c|3.0|A
>>> f|3.0|B
>>>
>>> (I am using sqlite 3.8.3 on Windows 8.1.)
>>> ___
>>> sqlite-users mailing list
>>> sqlite-users at mailinglists.sqlite.org
>>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>>
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thanks SQLite
On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin wrote:
>
>> I am noting an overlap with the things SQLite users have been using
>> sub-SELECTs for.
>>
>>
> Here is a self contained example that can be fed into the sqlite3 command
> line tool. It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
>(select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:
Well, you can, sort-of, if you use the CTE column naming. Consider this
simplified version of your Query (also using the CTE to do the actual
aggregate work once only):
create table Emp (emp text, salary real, dept text);
insert into Emp values
('a', 1, 'A'),
('b', 2, 'A'),
('c', 3, 'A'),
('d', 1, 'B'),
('e', 2, 'B'),
('f', 3, 'B');
WITH A(dept,avgSalary) as (
SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
)
SELECT Emp.*
FROM Emp, A
WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
-- emp | salary | dept
-- - | -- | -
-- c | 3.0 | A
-- f | 3.0 | B
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
> (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
> SELECT *
> FROM Emp AS e1
> WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thanks SQLite
On 2015-07-31 02:40 PM, Simon Slavin wrote: > > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. Quite, with the added advantage that you can reference the same CTE multiple times in later parts of the query, plus have two or more different aggregations on it in two or more different follow-up CTE's or later in the query - something not at all possible with a sub-select.
[sqlite] Thanks SQLite
On 31 Jul 2015, at 1:26pm, John McKown wrote: > WITH summary AS > ( SELECT var, count(var) as total FROM table GROUP BY key ) > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a > INNER JOIN summary AS b ON a.key=b.key > ORDER BY key Thank you at last for an example of non-recursive CTE being useful in the real world. I am noting an overlap with the things SQLite users have been using sub-SELECTs for. Simon.
[sqlite] Thanks SQLite
On 7/31/15, Gabor Grothendieck wrote: > Can we conclude, in general, that CTEs do not support > correlated subqueries? > For a recursive CTE, the recursive table reference must occur exactly once at the top-level, never in a subquery. Other than that, there are no restrictions on the use of subqueries in SQLite's CTE implementation, at least that I recall. -- D. Richard Hipp drh at sqlite.org
[sqlite] Thanks SQLite
Both the solutions transformed the correlated subquery into a join prior to
forming the CTE. Can we conclude, in general, that CTEs do not support
correlated subqueries?
On Fri, Jul 31, 2015 at 11:30 AM, R.Smith wrote:
>
>
> On 2015-07-31 03:40 PM, Gabor Grothendieck wrote:
>
>> On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin
>> wrote:
>>
>> I am noting an overlap with the things SQLite users have been using
>>> sub-SELECTs for.
>>>
>>>
>>> Here is a self contained example that can be fed into the sqlite3 command
>> line tool. It uses WITH to factor out the subquery; however, the annoying
>> part about it is that even though we have factored out the subquery we
>> still have to include a simplified version of the subquery, viz.
>>(select avgSalary from A)
>> It would have been nice if we could just replace (select avgSalary from A)
>> with (A.avgSalary) or even (A) but those substitutions do not work:
>>
>
> Well, you can, sort-of, if you use the CTE column naming. Consider this
> simplified version of your Query (also using the CTE to do the actual
> aggregate work once only):
>
>create table Emp (emp text, salary real, dept text);
>insert into Emp values
>('a', 1, 'A'),
>('b', 2, 'A'),
>('c', 3, 'A'),
>('d', 1, 'B'),
>('e', 2, 'B'),
>('f', 3, 'B');
>
>
>WITH A(dept,avgSalary) as (
> SELECT Emp.dept, AVG(salary) FROM Emp GROUP BY Emp.dept
>)
>SELECT Emp.*
> FROM Emp, A
> WHERE Emp.dept = A.dept AND Emp.salary > A.avgSalary;
>
>
> -- emp | salary | dept
> -- - | -- | -
> -- c | 3.0 | A
> -- f | 3.0 | B
>
>
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
>> create table Emp (emp text, salary real, dept text);
>> insert into Emp values ('a', 1, 'A');
>> insert into Emp values ('b', 2, 'A');
>> insert into Emp values ('c', 3, 'A');
>> insert into Emp values ('d', 1, 'B');
>> insert into Emp values ('e', 2, 'B');
>> insert into Emp values ('f', 3, 'B');
>> WITH A as
>> (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
>> SELECT *
>> FROM Emp AS e1
>> WHERE salary > (select avgSalary from A);
>>
>> Here is a sample run assuming the above has been placed into a file named
>> test.sqlite.
>>
>> C:\> sqlite3 < test.sqlite
>> c|3.0|A
>> f|3.0|B
>>
>> (I am using sqlite 3.8.3 on Windows 8.1.)
>> ___
>> sqlite-users mailing list
>> sqlite-users at mailinglists.sqlite.org
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
[sqlite] Thanks SQLite
On 07/30/2015 01:32 AM, Richard Hipp wrote: > On 7/29/15, R.Smith wrote: >> >> I just wish to ... display my gratitude ... for ... CTE's. >> > > Glad you find them useful. > > I'm looking for real-world (open-source) use cases for CTEs. Please > send me links find any. We use CTE in Skylable SX's job manager to track dependencies in a chain of jobs: http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5944 http://gitweb.skylable.com/gitweb/?p=sx.git;a=blob;f=server/src/fcgi/jobmgr.c;h=118d1d742e03c9404313d9f967eef58500e3b6c5;hb=HEAD#l5949 These chains are quite short (so that temp B-tree is not a performance problem) and could probably have been implemented with a loop in C and transactions, but it is very convenient to do it with just a single query. EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (SELECT job FROM jobs WHERE parent = :job UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET result = :res, reason = :reason, complete = 1, lock = NULL WHERE job IN (SELECT * FROM descendents_of) AND result = 0 0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 0 2|0|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 3|0|1|SCAN TABLE descendents_of 3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 EXPLAIN QUERY PLAN WITH RECURSIVE descendents_of(jb) AS (VALUES(:job) UNION SELECT job FROM jobs, descendents_of WHERE jobs.parent = descendents_of.jb) UPDATE jobs SET expiry_time = datetime(expiry_time, :ttldiff) WHERE job IN (SELECT * FROM descendents_of) 0|0|0|SEARCH TABLE jobs USING INTEGER PRIMARY KEY (rowid=?) 0|0|0|EXECUTE LIST SUBQUERY 0 3|0|1|SCAN TABLE descendents_of 3|1|0|SEARCH TABLE jobs USING COVERING INDEX jobs_parent (parent=?) 1|0|0|COMPOUND SUBQUERIES 0 AND 0 USING TEMP B-TREE (UNION) 0|0|0|SCAN SUBQUERY 1 Thanks, --Edwin
[sqlite] Thanks SQLite
On Fri, Jul 31, 2015 at 8:40 AM, Simon Slavin wrote:
>
> I am noting an overlap with the things SQLite users have been using
> sub-SELECTs for.
>
>
Here is a self contained example that can be fed into the sqlite3 command
line tool. It uses WITH to factor out the subquery; however, the annoying
part about it is that even though we have factored out the subquery we
still have to include a simplified version of the subquery, viz.
(select avgSalary from A)
It would have been nice if we could just replace (select avgSalary from A)
with (A.avgSalary) or even (A) but those substitutions do not work:
-- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
create table Emp (emp text, salary real, dept text);
insert into Emp values ('a', 1, 'A');
insert into Emp values ('b', 2, 'A');
insert into Emp values ('c', 3, 'A');
insert into Emp values ('d', 1, 'B');
insert into Emp values ('e', 2, 'B');
insert into Emp values ('f', 3, 'B');
WITH A as
(SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
SELECT *
FROM Emp AS e1
WHERE salary > (select avgSalary from A);
Here is a sample run assuming the above has been placed into a file named
test.sqlite.
C:\> sqlite3 < test.sqlite
c|3.0|A
f|3.0|B
(I am using sqlite 3.8.3 on Windows 8.1.)
[sqlite] Thanks SQLite
On Fri, Jul 31, 2015 at 8:40 AM, Gabor Grothendieck wrote:
> >
> Here is a self contained example that can be fed into the sqlite3 command
> line tool. It uses WITH to factor out the subquery; however, the annoying
> part about it is that even though we have factored out the subquery we
> still have to include a simplified version of the subquery, viz.
> (select avgSalary from A)
> It would have been nice if we could just replace (select avgSalary from A)
> with (A.avgSalary) or even (A) but those substitutions do not work:
>
> -- based on query at: https://en.wikipedia.org/wiki/Correlated_subquery
> ??
> create table Emp (emp text, salary real, dept text);
> insert into Emp values ('a', 1, 'A');
> insert into Emp values ('b', 2, 'A');
> insert into Emp values ('c', 3, 'A');
> insert into Emp values ('d', 1, 'B');
> insert into Emp values ('e', 2, 'B');
> insert into Emp values ('f', 3, 'B');
> WITH A as
> ??
> (SELECT AVG(salary) AS avgSalary FROM Emp WHERE dept = e1.dept)
> ??
> SELECT *
> FROM Emp AS e1
> WHERE salary > (select avgSalary from A);
>
> Here is a sample run assuming the above has been placed into a file named
> test.sqlite.
>
> C:\> sqlite3 < test.sqlite
> c|3.0|A
> f|3.0|B
>
> (I am using sqlite 3.8.3 on Windows 8.1.)
?Using SQLite 3.8.11 on Linux (RedHat Fedora 22), I used the following. I
don't know that it is "better", but doesn't use sub-selects, if that is of
any use.
sqlite> WITH a AS
...> (SELECT dept, AVG(salary) AS avgSalary FROM Emp GROUP BY dept)
...> select Emp.emp, salary, Emp.dept
...> FROM Emp JOIN a ON Emp.dept = a.dept AND salary > avgSalary;
c|3.0|A
f|3.0|B
I did an EXPLAIN QUERY PLAN on both of those. But I don't understand, at
all, what I am being told. I have posted the output for each, in the hopes
that somebody will tell me which is better.
>From the original, using the sub-select
0|0|0|SCAN TABLE Emp AS e1
0|0|0|EXECUTE CORRELATED SCALAR SUBQUERY 1
1|0|0|SCAN TABLE Emp
>From my alternate, using the JOIN with no sub-select
1|0|0|SCAN TABLE Emp
1|0|0|USE TEMP B-TREE FOR GROUP BY
0|0|0|SCAN TABLE Emp
0|1|1|SEARCH SUBQUERY 1 USING AUTOMATIC COVERING INDEX (dept=?)
?
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
[sqlite] Thanks SQLite
On Fri, Jul 31, 2015 at 7:40 AM, Simon Slavin wrote: > > On 31 Jul 2015, at 1:26pm, John McKown > wrote: > > > WITH summary AS > > ( SELECT var, count(var) as total FROM table GROUP BY key ) > > SELECT a.var, 100*a.var/b.total as percentage FROM table AS a > > INNER JOIN summary AS b ON a.key=b.key > > ORDER BY key > > Thank you at last for an example of non-recursive CTE being useful in the > real world. > > I am noting an overlap with the things SQLite users have been using > sub-SELECTs for. > ?Dang. That should be GROUP BY var, not GROUP BY key. Knew I'd mess up something.? > > Simon. > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > -- Schrodinger's backup: The condition of any backup is unknown until a restore is attempted. Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be. He's about as useful as a wax frying pan. 10 to the 12th power microphones = 1 Megaphone Maranatha! <>< John McKown
[sqlite] Thanks SQLite
I have also used CTE so that I could generate a "report" just using SQL. Something like (simplified): WITH summary AS ( SELECT var, count(var) as total FROM table GROUP BY key ) SELECT a.var, 100*a.var/b.total as percentage FROM table AS a INNER JOIN summary AS b ON a.key=b.key ORDER BY key ; ?Yes, I could do the equivalent in the program. But that does make it more complicated. Which makes it easier to make a mistake. And more difficult to modify. Being a bit weird, I have even encapsulated that in a BASH shell script which "transforms" a CSV file (simplified): #!/bin/sh if [ $# -le 3 ]; then echo "At least 3 arguments required." >2 echo "Argument 1 is the name of the input CSV." >2 echo "Argument 2 is the name of the output CSV. " >2 echo "Argument 3 is the name of the \"key\" field." >2 echo "All arguments after than are the names of other variable to include in the output." >2 echo "Note that variables in the input CSV which are not specifically named are excluded." >2 exit 1; fi input="$1" #name of input CSV file shift output="$2" #name of output CSV file shift key="$3" #name of the key field shift varlist="$@" #names of other variables to output, in order # Put in some percentages. sqlite3 << John McKown
[sqlite] Thanks SQLite
I dare to add my thanks here, with a much simpler example. Initially, for me, CTE was another thing to learn. Then I wanted SQLite to compute statistics on a simple two-column table. Not a big deal, I typed the equation and was done. Next day, I needed the same equation to be applied to two different columns, but these columns were to be produced from some other tables. Maybe there was a way to do it without, but I did it with WITH ... . Now, the equation stays put all the time and I change the WITH clause to redefine the two columns when I need equation to be applied to new data. I was elated when I implemented first equation. This is like stored procedure. Roman From: sqlite-users-bounces at mailinglists.sqlite.org [sqlite-users-bounces at mailinglists.sqlite.org] on behalf of R.Smith [rsm...@rsweb.co.za] Sent: Wednesday, July 29, 2015 10:08 PM To: sqlite-users at mailinglists.sqlite.org Subject: Re: [sqlite] Thanks SQLite On 2015-07-30 12:41 AM, Simon Slavin wrote: > On 29 Jul 2015, at 11:32pm, Richard Hipp wrote: > >> I'm looking for real-world (open-source) use cases for CTEs. > I would like to see a demonstration of how non-recursive CTEs can be useful > in simplifying a SQL command. Ideally in a plausible example rather than > something obviously made up with no real-world equivalent. It seems that > everyone who mentions CTEs jumps straight to recursion, as if that's the only > thing they're useful for. Well, it's hard to show a quick and simple example when the question specifically presupposes complexity, but if you forgive the verbosity, here is an example of non-recursive CTE which I don't think is even possible without CTE (or pre-set-up temporary tables) in a very real-World situation: Basic manufacturing company has a data system comprising of stock items and stockkeeping units (SKU's) aka manufactured products. The method of turning stock into a manufactured product is commonly referred to as a Job. Jobs have Bills of material (BOM) that basically is a stock item (sku) with a parent-child relationship to several other stock items which it "uses" or consumes to get made. A stock item can belong to very many Bills of material, or can be used outside of any (such as surgical gloves that makes part of the consumables for a job, but isn't part of the manufactured product, etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's Engine is its own manufactured BOM unit but also a sub-unit of the Car's total BOM. (Recursive CTE's help us a lot in this regard for working out other things) Now at any point, in your store there is an amount of stock that can be used, but the amount of a specific finished product that can be produced from it will vary widely according to BOM consumption figures. So let's say you have 10 of Stock X1, 20 of X2 and 5 of X3, but the product you want to make consumes 8 of each. You will need to buy 3 more X3 to be able to make even 1 of this product. That sounds simple enough, but consider that nobody will sell you 3 more X3, those X3 items comes in minimum orders of 25 (we say MOQ for minimum-order-quantity). So if I buy a pack of X3, I can now make one product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of X3 - that is more stock than I started with! Of course the MOQ for X1 and X2 are different, I might need to buy 30 of X1's and 10 of X2's. (There is also a thing called Pan-Size / Pack-Size that dictates the minimum set to buy - you might need to buy a minimum of 30, but after that you still need to buy sets of 6, so you can buy 36 or 42, but not 35 or 37, etc. I will ignore this bit for this example, but note how the complexity grows). Then, every item has a cost. Some items are very expensive, and some are very cheap. I won't mind buying 50 extra of cheap item A as long as I use up expensive item B completely. One common question is: How much do I need to buy of everything so that I can manufacture my products and have the least amount of wasted value left over?. A final complication to mention is that, we only really care about balancing after a point - I mean it's all fine and dandy if we find that making 7235 jobs will use up the stock so that there is zero of everything left. We can't make that many jobs, we don't have the capacity and even if we did, nobody will buy them all. In fact, for this exercise, we don't really wish to make more than 20 jobs at any time. By now I hopefully don't need to explain any more how this has become a really complex problem to solve. To answer the question, some CTEs can be called into service. First, let's build a table with job counts in the range that we are comfortable with (so 0 to 20 in this case). WITH JCount(c) AS ( SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 2
[sqlite] Thanks SQLite
On 2015-07-30 12:41 AM, Simon Slavin wrote: > On 29 Jul 2015, at 11:32pm, Richard Hipp wrote: > >> I'm looking for real-world (open-source) use cases for CTEs. > I would like to see a demonstration of how non-recursive CTEs can be useful > in simplifying a SQL command. Ideally in a plausible example rather than > something obviously made up with no real-world equivalent. It seems that > everyone who mentions CTEs jumps straight to recursion, as if that's the only > thing they're useful for. Well, it's hard to show a quick and simple example when the question specifically presupposes complexity, but if you forgive the verbosity, here is an example of non-recursive CTE which I don't think is even possible without CTE (or pre-set-up temporary tables) in a very real-World situation: Basic manufacturing company has a data system comprising of stock items and stockkeeping units (SKU's) aka manufactured products. The method of turning stock into a manufactured product is commonly referred to as a Job. Jobs have Bills of material (BOM) that basically is a stock item (sku) with a parent-child relationship to several other stock items which it "uses" or consumes to get made. A stock item can belong to very many Bills of material, or can be used outside of any (such as surgical gloves that makes part of the consumables for a job, but isn't part of the manufactured product, etc.). BOMs can be sub-BOMs of other BOMs, in the way that your car's Engine is its own manufactured BOM unit but also a sub-unit of the Car's total BOM. (Recursive CTE's help us a lot in this regard for working out other things) Now at any point, in your store there is an amount of stock that can be used, but the amount of a specific finished product that can be produced from it will vary widely according to BOM consumption figures. So let's say you have 10 of Stock X1, 20 of X2 and 5 of X3, but the product you want to make consumes 8 of each. You will need to buy 3 more X3 to be able to make even 1 of this product. That sounds simple enough, but consider that nobody will sell you 3 more X3, those X3 items comes in minimum orders of 25 (we say MOQ for minimum-order-quantity). So if I buy a pack of X3, I can now make one product - but, I will now be left over with 2 of X1, 12 of X2 and 22 of X3 - that is more stock than I started with! Of course the MOQ for X1 and X2 are different, I might need to buy 30 of X1's and 10 of X2's. (There is also a thing called Pan-Size / Pack-Size that dictates the minimum set to buy - you might need to buy a minimum of 30, but after that you still need to buy sets of 6, so you can buy 36 or 42, but not 35 or 37, etc. I will ignore this bit for this example, but note how the complexity grows). Then, every item has a cost. Some items are very expensive, and some are very cheap. I won't mind buying 50 extra of cheap item A as long as I use up expensive item B completely. One common question is: How much do I need to buy of everything so that I can manufacture my products and have the least amount of wasted value left over?. A final complication to mention is that, we only really care about balancing after a point - I mean it's all fine and dandy if we find that making 7235 jobs will use up the stock so that there is zero of everything left. We can't make that many jobs, we don't have the capacity and even if we did, nobody will buy them all. In fact, for this exercise, we don't really wish to make more than 20 jobs at any time. By now I hopefully don't need to explain any more how this has become a really complex problem to solve. To answer the question, some CTEs can be called into service. First, let's build a table with job counts in the range that we are comfortable with (so 0 to 20 in this case). WITH JCount(c) AS ( SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 21 ), (Ignore the recursion here, it only builds a quick little table, it isn't recursing the main dataset in any way - just another way of saying SELECT 1 UNION ALL SELECT 2 UNION ALL.. etc etc.) Next we will add a CTE that lists Job candidates for manufacture, followed by a CTE that calculates all our candidate BOMs and their stock needs. I'll imagine this is a sweets factory and we're making little jelly things this time. (I'm not showing the schema because it isn't important and you can intuit it) WITH JCount(c) AS ( SELECT 0 UNION ALL SELECT c+1 FROM JCount LIMIT 20 ), Candidates(BOMCode, UnitsPerBOM) AS ( SELECT StockCode, QtyPer FROM stock WHERE Description LIKE '%jelly%' AND StockCode IN (SELECT ParentPart FROM BomStructure) ), JBOMs(JobQty, BOMCode, TotalUnitsToMake, TotalStockNeeded) AS ( SELECT c, BOMCode, (c * UnitsPerBOM), (QtyPer * c) FROM JCount, Candidates JOIN stock ON stock.StockCode IN (SELECT Component FROM BomStructure WHERE ParentPart = BOMCode) WHERE stock.QtyPer > 0 )
[sqlite] Thanks SQLite
I just wish to note something and display my gratitude (which I hope is shared by others) towards Richard, Dan and other SQLite devs for all the great functionality, but specifically CTE's. The reason I need to say this is that I was one of the people who, when we first had requests for CTE's on this forum (if anyone fancies scrolling back), said: "C-T-E - Shmee-Tee-Eee - who would ever need that for real?" chimed along with others who had statements like "Anything you can do with CTE you can also do without it" and the like. Today the inverse is true. What an absolute pleasure to use. Even if the CTE may not be strictly needed, almost every problem or complex query can be simplified using a CTE, and easily later factored back (removing the CTE) if optimization requires it. I find also explaining query concepts to others via a CTE much easier. I now use CTE's extensively in MSSQL (thanks to SQLite showing the way) and get annoyed at MariaDB/MySQL for not having it. This also makes up amicably for SQLite's lack of script variables. Just make a CTE table full of presets to use later - problem solved. A heartfelt Thank-you! Ryan
[sqlite] Thanks SQLite
On 29 Jul 2015, at 11:32pm, Richard Hipp wrote: > I'm looking for real-world (open-source) use cases for CTEs. I would like to see a demonstration of how non-recursive CTEs can be useful in simplifying a SQL command. Ideally in a plausible example rather than something obviously made up with no real-world equivalent. It seems that everyone who mentions CTEs jumps straight to recursion, as if that's the only thing they're useful for. Simon.
[sqlite] Thanks SQLite
On 7/29/15, R.Smith wrote: > > I just wish to ... display my gratitude ... for ... CTE's. > Glad you find them useful. I'm looking for real-world (open-source) use cases for CTEs. Please send me links find any. I have spotted a few CTEs used in Firefox. For example, when rearranging the bookmark hierarchy by drag-and-drop in Firefox, it uses a CTE to verify that you are not trying to make a bookmark folder into a subfolder of itself. And, of course, Fossil uses CTEs (since I wrote them). But more examples would be appreciated. > I now get annoyed at > MariaDB/MySQL for not having it. > FWIW: I am told that MariaDB will have CTEs soon - within 6 months or so. -- D. Richard Hipp drh at sqlite.org
[sqlite] Thanks to Igor Tandetnik for providing help.
Dear Igor Tandetnik Thank you for teaching me how to query data duplicatee more than 2 from table. It is really helpful to me. Best Regards tom 2012/7/30 > Send sqlite-users mailing list submissions to > sqlite-users@sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body 'help' to > sqlite-users-requ...@sqlite.org > > You can reach the person managing the list at > sqlite-users-ow...@sqlite.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqlite-users digest..." > > > Today's Topics: > >1. Could anyone recommend books for SQLite (???) >2. How to query data in table more than 2? (???) >3. Re: Could anyone recommend books for SQLite (Peter Aronson) >4. Re: How to query data in table more than 2? (Igor Tandetnik) >5. Re: MIN() for a timedelta? (C M) >6. Re: Handling concurrent read request to SQLite (Navaneeth.K.N) >7. Unknown module FTS4 (Navaneeth.K.N) >8. how to process password (yanhong.ye) >9. open database on Linux. Already db created on Mac. (Durga D) > 10. Re: EXT : Unknown module FTS4 (Black, Michael (IS)) > 11. Re: EXT : open database on Linux. Already db created on Mac. > (Black, Michael (IS)) > 12. Re: how to process password (Robert Myers) > > > -- > > Message: 1 > Date: Sun, 29 Jul 2012 09:44:56 +0800 > From: ??? > To: sqlite-users@sqlite.org > Subject: [sqlite] Could anyone recommend books for SQLite > Message-ID: > < > cad70eapwdpr6m0ohzscbgxfg-gded_kojvaq+oeapsufv56...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Dear all > I am looking for a book ,which include all instructures and functions > of SQLite. It is like reference guide. > Could any help? > your sincerely > tom > > > -- > > Message: 2 > Date: Sun, 29 Jul 2012 09:50:03 +0800 > From: ??? > To: sqlite-users@sqlite.org > Subject: [sqlite] How to query data in table more than 2? > Message-ID: > < > cad70eam+hcrddy93c1zoxn6z-ez7+c-dblcwdswps1enk8p...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Dear all: >I would like to query customer from from ooder table and only customer > order more than twice. > Which clause or filter should I use to filter those customer order more > than twice ? > Please help > Best regards > tom > > > -- > > Message: 3 > Date: Sat, 28 Jul 2012 18:50:19 -0700 > From: Peter Aronson > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Could anyone recommend books for SQLite > Message-ID: <501496db.90...@att.net> > Content-Type: text/plain; charset=UTF-8; format=flowed > > There's a page on the SQLite website about SQLite books at > http://www.sqlite.org/books.html. I own the first two on the page, and > like them both. If I was only to get one SQLite book, it'd be the second > one on the list: Using SQLite by JayKreibich. > > Best, > > Peter > > On 7/28/2012 6:44 PM, ??? wrote: > > Dear all > > I am looking for a book ,which include all instructures and > functions > > of SQLite. It is like reference guide. > > Could any help? > > your sincerely > > tom > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > > Message: 4 > Date: Sat, 28 Jul 2012 23:46:56 -0400 > From: "Igor Tandetnik" > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to query data in table more than 2? > Message-ID: > Content-Type: text/plain; charset="utf-8" > > ??? wrote: > > I would like to query customer from from ooder table and only customer > > order more than twice. > > select CustomerId from Orders group by CustomerId having count(*) > 2; > > -- > Igor Tandetnik > > > > -- > > Message: 5 > Date: Sat, 28 Jul 2012 23:57:20 -0400 > From: C M > To: General Discussion of SQLite Database > Subject: Re: [sqlite] MIN() for a timedelta? > Message-ID: > 4hps7c6ks+pbr1uk4_wewqy28cdgzyfyh...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Thanks everyone for the various replies and help. Very useful and I > will look into the differences and if I have questions about how these > work will let you know. > > Thank you, > Che > > > -- > > Message: 6 > Date: Sun, 29 Jul 2012 12:38:07 +0530 > From: "Navaneeth.K.N" > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Handling concurrent read request to SQLite > Message-ID: > 14sdg00pgbdwr0vevynogypcqk+7arq...@mail.gmail.com> > Content-Type: text/plain; charset=UTF-8 > > On Thu, Jul 26, 2012 at 11:37 AM, Simon Slavin > wrote: > > > > > Have you
Re: [sqlite] Thanks to Peter Aronson for providing help!
Dear Peter: "Using SQLite" is on my listing to purchase. It is really helpful for me to understand futher in SQLite. Thank you again. Best Regards Tom 2012/7/30 > Send sqlite-users mailing list submissions to > sqlite-users@sqlite.org > > To subscribe or unsubscribe via the World Wide Web, visit > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > or, via email, send a message with subject or body 'help' to > sqlite-users-requ...@sqlite.org > > You can reach the person managing the list at > sqlite-users-ow...@sqlite.org > > When replying, please edit your Subject line so it is more specific > than "Re: Contents of sqlite-users digest..." > > > Today's Topics: > >1. Could anyone recommend books for SQLite (???) >2. How to query data in table more than 2? (???) >3. Re: Could anyone recommend books for SQLite (Peter Aronson) >4. Re: How to query data in table more than 2? (Igor Tandetnik) >5. Re: MIN() for a timedelta? (C M) >6. Re: Handling concurrent read request to SQLite (Navaneeth.K.N) >7. Unknown module FTS4 (Navaneeth.K.N) >8. how to process password (yanhong.ye) >9. open database on Linux. Already db created on Mac. (Durga D) > 10. Re: EXT : Unknown module FTS4 (Black, Michael (IS)) > 11. Re: EXT : open database on Linux. Already db created on Mac. > (Black, Michael (IS)) > 12. Re: how to process password (Robert Myers) > > > -- > > Message: 1 > Date: Sun, 29 Jul 2012 09:44:56 +0800 > From: ??? > To: sqlite-users@sqlite.org > Subject: [sqlite] Could anyone recommend books for SQLite > Message-ID: > < > cad70eapwdpr6m0ohzscbgxfg-gded_kojvaq+oeapsufv56...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Dear all > I am looking for a book ,which include all instructures and functions > of SQLite. It is like reference guide. > Could any help? > your sincerely > tom > > > -- > > Message: 2 > Date: Sun, 29 Jul 2012 09:50:03 +0800 > From: ??? > To: sqlite-users@sqlite.org > Subject: [sqlite] How to query data in table more than 2? > Message-ID: > < > cad70eam+hcrddy93c1zoxn6z-ez7+c-dblcwdswps1enk8p...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Dear all: >I would like to query customer from from ooder table and only customer > order more than twice. > Which clause or filter should I use to filter those customer order more > than twice ? > Please help > Best regards > tom > > > -- > > Message: 3 > Date: Sat, 28 Jul 2012 18:50:19 -0700 > From: Peter Aronson > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] Could anyone recommend books for SQLite > Message-ID: <501496db.90...@att.net> > Content-Type: text/plain; charset=UTF-8; format=flowed > > There's a page on the SQLite website about SQLite books at > http://www.sqlite.org/books.html. I own the first two on the page, and > like them both. If I was only to get one SQLite book, it'd be the second > one on the list: Using SQLite by JayKreibich. > > Best, > > Peter > > On 7/28/2012 6:44 PM, ??? wrote: > > Dear all > > I am looking for a book ,which include all instructures and > functions > > of SQLite. It is like reference guide. > > Could any help? > > your sincerely > > tom > > ___ > > sqlite-users mailing list > > sqlite-users@sqlite.org > > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > > > > > > -- > > Message: 4 > Date: Sat, 28 Jul 2012 23:46:56 -0400 > From: "Igor Tandetnik" > To: sqlite-users@sqlite.org > Subject: Re: [sqlite] How to query data in table more than 2? > Message-ID: > Content-Type: text/plain; charset="utf-8" > > ??? wrote: > > I would like to query customer from from ooder table and only customer > > order more than twice. > > select CustomerId from Orders group by CustomerId having count(*) > 2; > > -- > Igor Tandetnik > > > > -- > > Message: 5 > Date: Sat, 28 Jul 2012 23:57:20 -0400 > From: C M > To: General Discussion of SQLite Database > Subject: Re: [sqlite] MIN() for a timedelta? > Message-ID: > 4hps7c6ks+pbr1uk4_wewqy28cdgzyfyh...@mail.gmail.com> > Content-Type: text/plain; charset=ISO-8859-1 > > Thanks everyone for the various replies and help. Very useful and I > will look into the differences and if I have questions about how these > work will let you know. > > Thank you, > Che > > > -- > > Message: 6 > Date: Sun, 29 Jul 2012 12:38:07 +0530 > From: "Navaneeth.K.N" > To: General Discussion of SQLite Database > Subject: Re: [sqlite] Handling concurrent read request to SQLite > Message-ID: > 14sdg00pgbdwr0vevynogypcqk+7arq...@mail.gmail.com> > Content-Type: text/plain; charset=UTF-8 > > On Thu, Jul 26, 2012 at 11:37 AM, Simon Slavin > wrote: > > > > > Have
Re: [sqlite] Thanks for making my product possible
Seconded! Give yourself a raise! ;) I've seen Sqlite popping up all over. I was particularly pleased to see it being supported as a data source option for open office. On 11/1/05, Edward Wilson <[EMAIL PROTECTED]> wrote: > Ditto - sqlite is pure beauty - thanks. > > --- Clay Dowling <[EMAIL PROTECTED]> wrote: > > Amid all the wailing and gnashing of teeth I thought that I'd just say > > thanks for making a great embeddable database that puts a very minimal > > burden on the developer. My product, at least, would never have seen
Re: [sqlite] Thanks for making my product possible
Ditto - sqlite is pure beauty - thanks. --- Clay Dowling <[EMAIL PROTECTED]> wrote: > Amid all the wailing and gnashing of teeth I thought that I'd just say > thanks for making a great embeddable database that puts a very minimal > burden on the developer. My product, at least, would never have seen [snip] __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
[sqlite] Thanks for making my product possible
Amid all the wailing and gnashing of teeth I thought that I'd just say thanks for making a great embeddable database that puts a very minimal burden on the developer. My product, at least, would never have seen the light of day without SQLite. I intend to continue using SQLite into the future, no matter what SELECT 5/2 returns in its result set. Clay Dowling -- CeaMuS, Simple Content Management http://www.ceamus.com
Re: [sqlite] Thanks
On 2005-05-23T17:29:14+0200, Joel Rosdahl wrote: > > Looking forward to a python db compliant client for 3.x hitting the > > Debian archives (apsw is nice, I am sure, but not what I want for > > the project that I am working on). > > PySQLite 2.0.2 now exists in Debian unstable: > > http://packages.debian.org/python-pysqlite2 > > I hope it works for you. :-) Installed and in use since yesterday :-) /Allan signature.asc Description: Digital signature
Re: [sqlite] Thanks
[EMAIL PROTECTED] (Allan Wind) writes: > Looking forward to a python db compliant client for 3.x hitting the > Debian archives (apsw is nice, I am sure, but not what I want for > the project that I am working on). PySQLite 2.0.2 now exists in Debian unstable: http://packages.debian.org/python-pysqlite2 I hope it works for you. :-) Regards, Joel -- Joel Rosdahl <[EMAIL PROTECTED]> Key BB845E97; fingerprint 9F4B D780 6EF4 5700 778D 8B22 0064 F9FF BB84 5E97
[sqlite] Thanks
Just a quick note letting you know that sqlite is working great here. Thanks for making such a wonderful tool available to the world. Only bump that I hit was that python-sqlite 1.0.1 automatically decodes BLOBs, which did not jive with inserted MIME enocede data using `sqlite`. Switching to TEXT solved that. Looking forward to a python db compliant client for 3.x hitting the Debian archives (apsw is nice, I am sure, but not what I want for the project that I am working on). /Allan signature.asc Description: Digital signature
RE: [sqlite] Thanks!
-Original Message- From: Jay [mailto:[EMAIL PROTECTED] Sent: Thursday, March 03, 2005 1:43 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Thanks! > >I believe writing C or C++ code is harder than writing interpreted > >code. My aim has always been to produce the best product I could, > >not to produce it with as little effort as possible. I hope the > >extra effort was worth it. I guess time will tell if I chose > >correctly. > > One caveat of languages like C and C++ is that you are opening > yourself to several classes of potential security problems that > interpreted languages tend not to have. You have to deal with things > > like buffer overflow attacks, one of the more common kind, that can > come from you not crossing every "t" and dotting every "i" with your > memory management. Interpreted languages take care of these sorts of > > things for you, so they are orders of magnitude more secure by > default. You have to put in a lot of effort with C and C++ to be > just as secure. (Sure, the interpreter engines themselves could have > > such problems, but they are exceedingly rare due to their common code > > being heavily used and examined by a relatively large group.) The > main advantages of C is that you can get the smallest possible > footprint and greatest possible speed; it is best applied to what > would be bottlenecks. -- Darren Duncan One of the things the C++ experts take particular care to remind everyone is that character arrays are evil. I thought using string classes mostly elminated the buffer overflow problem. The string class is heavily examined for such errors as are the intrepreters. I'm seeing reports of vulnerabilities in interpreted languages so I'm not sure I agree with that logic, or the orders of magnitude comparison. Do you have any references or research on it? Interpreter authors write code with errors too. If the interpreter gets tweaked every month the code only gets 30 days of testing. Few of the packages I've seen have any regression testing so new errors can be introduced with each revision and old errors can reappear. I should go look and see how they cracked php and string overflows. I just consider it bare metal programming envy :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Thanks!
[EMAIL PROTECTED] wrote: After 15 years of assembler programming, I am still to find a compiler that makes debugging and optimizing as easy as assembler. I can't remember the number of times that C has got me deep into memory leaks. Then give C++ a try. If you need low level programming, C is a good compromise. If you need high level programming, C is a good compromise. If you don't care about performance and need an idiot proof programming language, try Java or perl. Obviously C has a place but the day Java performs as well as C, who would one to deal with all of those "shot your own foot" issues. Java provides its own ways of shooting yourself in the foot. See the "finally" kludge for example. -- http://www.velocityvector.com/ | http://glmiller.blogspot.com/ http://www.classic-games.com/ | "We have declared a fierce war on this evil principle of democracy and those who follow this wrong ideology," -- Abu Musab al-Zarqawi
Re: [sqlite] Thanks!
At 11:43 AM -0800 3/3/05, Jay wrote: One of the things the C++ experts take particular care to remind everyone is that character arrays are evil. I thought using string classes mostly elminated the buffer overflow problem. The string class is heavily examined for such errors as are the intrepreters. I'm seeing reports of vulnerabilities in interpreted languages so I'm not sure I agree with that logic, or the orders of magnitude comparison. Do you have any references or research on it? Interpreter authors write code with errors too. If the interpreter gets tweaked every month the code only gets 30 days of testing. Few of the packages I've seen have any regression testing so new errors can be introduced with each revision and old errors can reappear. I should go look and see how they cracked php and string overflows. I say "orders of magnitude" if a class of errors is the one that occurs the most frequently, and only appears with the compiled languages. By emiminating the most common problems, orders of magnitude improvement is gained. With interpreters, any buffer overflows etc are only in the interpreter itself, and can never be in your own code written in the interpreted language. Usually, the amount of code in the interpreter is many orders of magnitude smaller than the amount of code that is written in the interpreted language. With C/C++ code, the entire code base can be suspect, but in an interpreted language, only the tiny fraction of the code making up the interpreter itself is suspect. When the code base that can have such problems is a lot smaller, the total number of actual problems is smaller. The greater part of my experience with interpreted languages is with Perl 5.8, which is used by tens or hundreds of thousands of programmers regularly, and whose source code is scrutinized and worked on by probably several dozens of people each day. Perl 5.8 also has a large and comprehensive unit test suite, including regression tests. It is very hard for bugs to get by it. Let me know if you're aware of any security vulnerabilities with Perl 5.8. The Parrot project also has strong regression testing, though that project is pre-alpha. As for PHP, well I consider that specifically to be less elegant than many other interpreted languages, and much more likely to be buggy. This has a lot to do with its ad-hoc design principle, just throwing in features wherever, rather than having a more thought out system design. All of the above being said, you do gain a lot of the same advantages of interpreted languages in C/C++ in regards to bugs and security when you use a library that is already well written to handle common tasks, as some other posters here mentioned. In that respect, the interpreted language is alike, since you are implicitly using libraries rather than explicitly. -- Darren Duncan
Re: [sqlite] Thanks!
Jay said: > I used cgicc. > It's good workable code, but it's a pain sometimes to understand! Agreed. In fact I use cgic in either C or C++, since it's easy to use. The licensing is agreeable too, since even a commercial license is very inexpensive. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Thanks!
> like try to write your own CGI parser. Libraries like cgic at > http://www.boutell.com/cgic/ and cgicc at http://www.cgicc.org make a > lot > more sense than reinventing that particular wheel yourself. They > also > address the buffer overflow problem directly by using strings, or > forcing > the author to state how much data they are capable of receiving. > > Now that I have my infrastructure libraries, C/C++ and PHP are about > equivalent in speed for me to develop an app, and if complexity is > involved C++ will be the best bet. I used cgicc. It's good workable code, but it's a pain sometimes to understand! __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/
Re: [sqlite] Thanks!
--- [EMAIL PROTECTED] wrote: > Agree, C is great and thanks God we got it in our asenal of tools. > But it > is not pefect because of its own strengths. > After 15 years of assembler programming, I am still to find a > compiler that > makes debugging and optimizing as easy as assembler. > I can't remember the number of times that C has got me deep into > memory > leaks. > If you need low level programming, C is a good compromise. > If you need high level programming, C is a good compromise. > If you don't care about performance and need an idiot proof > programming > language, try Java or perl. > Obviously C has a place but the day Java performs as well as C, who > would > one to deal with all of those "shot your own foot" issues. > Those are my 2 cents I am not trying to offend anybody. > No worries :) I have much the same experience. Sorry if I came across as offended. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Thanks!
Agree, C is great and thanks God we got it in our asenal of tools. But it
is not pefect because of its own strengths.
After 15 years of assembler programming, I am still to find a compiler that
makes debugging and optimizing as easy as assembler.
I can't remember the number of times that C has got me deep into memory
leaks.
If you need low level programming, C is a good compromise.
If you need high level programming, C is a good compromise.
If you don't care about performance and need an idiot proof programming
language, try Java or perl.
Obviously C has a place but the day Java performs as well as C, who would
one to deal with all of those "shot your own foot" issues.
Those are my 2 cents I am not trying to offend anybody.
Regards,
Uriel_Carrasquilla
Jay
<[EMAIL PROTECTED]To:
sqlite-users@sqlite.org
oo.com> cc:
Subject: Re: [sqlite] Thanks!
03/03/2005 02:56
PM
Please respond to
sqlite-users
>
> can a similar argument be made for assembler?
Yes, but modern C compilers generate code that's 99% as good as
a human would write. I've tried to write better code in assembler
than the compiler does. I've managed it, but's it's pretty hard.
If you profile your code you generally find that the difference
between C and assembler to be not significant compared to other
factors. In my case speed is not material because even the worst
code on a really slow machine won't make the internet send the
data any faster.
The only place I've seen assembler used, aside from in the
computer in your microwave oven, is in the innermost rendering
loop in 3d game engines.
__
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
Re: [sqlite] Thanks!
Darren Duncan said: > One caveat of languages like C and C++ is that you are opening > yourself to several classes of potential security problems that > interpreted languages tend not to have. You are opening yourself to such problems only if you do something rash like try to write your own CGI parser. Libraries like cgic at http://www.boutell.com/cgic/ and cgicc at http://www.cgicc.org make a lot more sense than reinventing that particular wheel yourself. They also address the buffer overflow problem directly by using strings, or forcing the author to state how much data they are capable of receiving. Now that I have my infrastructure libraries, C/C++ and PHP are about equivalent in speed for me to develop an app, and if complexity is involved C++ will be the best bet. Clay -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
Re: [sqlite] Thanks!
> > can a similar argument be made for assembler? Yes, but modern C compilers generate code that's 99% as good as a human would write. I've tried to write better code in assembler than the compiler does. I've managed it, but's it's pretty hard. If you profile your code you generally find that the difference between C and assembler to be not significant compared to other factors. In my case speed is not material because even the worst code on a really slow machine won't make the internet send the data any faster. The only place I've seen assembler used, aside from in the computer in your microwave oven, is in the innermost rendering loop in 3d game engines. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Thanks!
> >I believe writing C or C++ code is harder than writing interpreted > >code. My aim has always been to produce the best product I could, > >not to produce it with as little effort as possible. I hope the > >extra effort was worth it. I guess time will tell if I chose > >correctly. > > One caveat of languages like C and C++ is that you are opening > yourself to several classes of potential security problems that > interpreted languages tend not to have. You have to deal with things > > like buffer overflow attacks, one of the more common kind, that can > come from you not crossing every "t" and dotting every "i" with your > memory management. Interpreted languages take care of these sorts of > > things for you, so they are orders of magnitude more secure by > default. You have to put in a lot of effort with C and C++ to be > just as secure. (Sure, the interpreter engines themselves could have > > such problems, but they are exceedingly rare due to their common code > > being heavily used and examined by a relatively large group.) The > main advantages of C is that you can get the smallest possible > footprint and greatest possible speed; it is best applied to what > would be bottlenecks. -- Darren Duncan One of the things the C++ experts take particular care to remind everyone is that character arrays are evil. I thought using string classes mostly elminated the buffer overflow problem. The string class is heavily examined for such errors as are the intrepreters. I'm seeing reports of vulnerabilities in interpreted languages so I'm not sure I agree with that logic, or the orders of magnitude comparison. Do you have any references or research on it? Interpreter authors write code with errors too. If the interpreter gets tweaked every month the code only gets 30 days of testing. Few of the packages I've seen have any regression testing so new errors can be introduced with each revision and old errors can reappear. I should go look and see how they cracked php and string overflows. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: [sqlite] Thanks!
On Thu, 2005-03-03 at 11:06 -0800, Darren Duncan wrote: > The main advantages of C is that you can get the smallest possible > footprint and greatest possible speed; > That is not the reason CVSTrac is written in C. CVSTrac is written in C to reduce its administration burden. You can drop a statically-linked C program in a cgi-bin directory and it just works. With scripts, it is usually not so simple. -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thanks!
can a similar argument be made for assembler? Regards, [EMAIL PROTECTED] NCCI Boca Raton, Florida 561.893.2415 greetings / avec mes meilleures salutations / Cordialmente mit freundlichen Grüßen / Med vänlig hälsning Darren Duncan <[EMAIL PROTECTED]To: sqlite-users@sqlite.org can.net> cc: Subject: Re: [sqlite] Thanks! 03/03/2005 02:06 PM Please respond to sqlite-users At 7:27 AM -0800 3/3/05, Jay wrote: >I believe writing C or C++ code is harder than writing interpreted >code. My aim has always been to produce the best product I could, >not to produce it with as little effort as possible. I hope the >extra effort was worth it. I guess time will tell if I chose >correctly. One caveat of languages like C and C++ is that you are opening yourself to several classes of potential security problems that interpreted languages tend not to have. You have to deal with things like buffer overflow attacks, one of the more common kind, that can come from you not crossing every "t" and dotting every "i" with your memory management. Interpreted languages take care of these sorts of things for you, so they are orders of magnitude more secure by default. You have to put in a lot of effort with C and C++ to be just as secure. (Sure, the interpreter engines themselves could have such problems, but they are exceedingly rare due to their common code being heavily used and examined by a relatively large group.) The main advantages of C is that you can get the smallest possible footprint and greatest possible speed; it is best applied to what would be bottlenecks. -- Darren Duncan
Re: [sqlite] Thanks!
At 7:27 AM -0800 3/3/05, Jay wrote: I believe writing C or C++ code is harder than writing interpreted code. My aim has always been to produce the best product I could, not to produce it with as little effort as possible. I hope the extra effort was worth it. I guess time will tell if I chose correctly. One caveat of languages like C and C++ is that you are opening yourself to several classes of potential security problems that interpreted languages tend not to have. You have to deal with things like buffer overflow attacks, one of the more common kind, that can come from you not crossing every "t" and dotting every "i" with your memory management. Interpreted languages take care of these sorts of things for you, so they are orders of magnitude more secure by default. You have to put in a lot of effort with C and C++ to be just as secure. (Sure, the interpreter engines themselves could have such problems, but they are exceedingly rare due to their common code being heavily used and examined by a relatively large group.) The main advantages of C is that you can get the smallest possible footprint and greatest possible speed; it is best applied to what would be bottlenecks. -- Darren Duncan
Re: [sqlite] Thanks!
> > * An event management web site for a convention. > > * Gentoo 2004 > > * Linux 2.4 kernel > > * AMD Duron 600mhz w/ 256 meg RAM > > * lighttpd web server > > * C++ cgi > > * sqlite backend database > > I'm heartily glad to hear that I'm not the only poor deluded fool > writing > CGI apps in C and C++. There's an article running in this month's > Linux > Journal on the topic (I'm the author). I found it interesting that > Reuven > Lerner, in his "At The Forge" column in the same issue, mentioned > that > hardly anybody uses C for CGI development. He's right, but I still > found > it humorous given the juxtaposition. Congrats on getting your article published! :) After being hacked using other distros several times I decided to get serious about security. I also wrote about my adventures in one of the Linux magazines. I picked Gentoo because I could customize my system to *any* degree I desired. That box has almost nothing on it that isn't necessary for its function. The stuff it does run has as few features as possible. Less is more when it reduces vulnerabilities. I haven't been hacked since so it must be working, or I'm very lucky. The minimalist approach also helps it perform well on what's considered 'obsolete' hardware. The previous server was even smaller hardware wise and served 3+ static pages per day for years. The new server will have more work to do with the dynamic pages. Part of the minimalist approach was to not use interpreted code in web pages. It's harder to hack the box if there's no perl/php/python/ruby interpreter available. The Lighttpd and Boa webservers perform very well with minimal overhead. They both avoid the use of threads and forking for speed. Lighttpd adds the ability to use ssl and access control that boa lacks. I've been told interpreted languages can run as fast as compiled but I've not seen a good example of it. The executable for the interpreter alone is larger than a good fraction of my cgi apps added together. There's enough RAM in the box I suspect everything is in cache, so load time may not be a non issue. I would bet my raw executable is faster than interpreted code, but that's probably not an issue either since the bottleneck is almost certainly the connection to the internet. I probably assemble packets pretty quickly, but if they don't get to the user any faster who cares? I've considered rewriting in FastCGI but so far the performance seems more than adequate. Sqlite performs very well speed wise. It does what I need and helped me remove one more possible vulnerability. If there's no database server nobody can hack it! The only thing I could wish for is the ability to change the locking methodology on the fly. There are times when I really do want to do dirty reads. It's probably not going to be an issue until I get as busy as google though. I believe writing C or C++ code is harder than writing interpreted code. My aim has always been to produce the best product I could, not to produce it with as little effort as possible. I hope the extra effort was worth it. I guess time will tell if I chose correctly. Thanks for letting me know I'm not the only regressive weirdo out there! __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/
Re: [sqlite] Thanks!
On Thu, 2005-03-03 at 08:33 -0500, Clay Dowling wrote: > I'm heartily glad to hear that I'm not the only poor deluded fool writing > CGI apps in C and C++. FWIW, the CVSTrac program used for bug tracking on www.sqlite.org is a C program that runs as CGI and uses SQLite as its database backend. http://www.cvstrac.org/ -- D. Richard Hipp <[EMAIL PROTECTED]>
Re: [sqlite] Thanks!
Jay said: > My project: > > * An event management web site for a convention. > * Gentoo 2004 > * Linux 2.4 kernel > * AMD Duron 600mhz w/ 256 meg RAM > * lighttpd web server > * C++ cgi > * sqlite backend database I'm heartily glad to hear that I'm not the only poor deluded fool writing CGI apps in C and C++. There's an article running in this month's Linux Journal on the topic (I'm the author). I found it interesting that Reuven Lerner, in his "At The Forge" column in the same issue, mentioned that hardly anybody uses C for CGI development. He's right, but I still found it humorous given the juxtaposition. Clay Dowling -- Lazarus Notes from Lazarus Internet Development http://www.lazarusid.com/notes/ Articles, Reviews and Commentary on web development
[sqlite] Thanks!
I've gotten my first application up and running nicely with sqlite. I wanted to say thanks to everyone for their hard work that made it possible more quickly than I had imagined. My project: * An event management web site for a convention. * Gentoo 2004 * Linux 2.4 kernel * AMD Duron 600mhz w/ 256 meg RAM * lighttpd web server * C++ cgi * sqlite backend database __ Celebrate Yahoo!'s 10th Birthday! Yahoo! Netrospective: 100 Moments of the Web http://birthday.yahoo.com/netrospective/
[sqlite] Thanks
Hello everybody, again ;) I got a mail from Stephen Drew where he explained that my SQL was wrong and he also wrote how i should do an UNION ALL with GROUP BY. The following text is taken from Stephens mail that i received: > An order by for the whole UNION should be: > > SELECT test1 FROM > ( >SELECT test1 FROM table1 >UNION ALL >SELECT test1 FROM table2 > ) > ORDER BY test1; Thanks! Ps. Sorry for posting my question twice but i got an error from "my" SMTP server. -- Best regards, Andox - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
