Re: [sqlite] Feature Request: Binding Arrays

[Olaf Schmidt]

Am 31.10.2013 14:09, schrieb Dominique Devienne:

[Userdefined functions in conjunction with fast Exists-checks
in "Userland" - vs. SQLites built-in indexing in case of In (List)]


I'm not convinced by this. The "real table" can be quite large, several
100's to 100,000's rows (up to 1+ million rows) and col can be the primary
key, or a non-unique "parent" key where many parent keys have about 10 rows
each, and a few have in the 1000's, while the in-list could very small
(down to just 1 element) or quite large (several thousands).

With a function based approach, you are *always* full-scanning the whole
"real" table, no matter the cardinality of the InList operand, and even
with a very fast InList function, this is not going to beat getting 10 PK
rows, or 10 "parent" key rows (e.g. 100 rows to 10,000 rows) via indexes,
especially since these are virtual tables with Boost.MultiIndex unique or
non-unique indexes (i.e. 5x to 10x faster than SQLite's paged B-tree
indexes). It might well beat it if the InList operand cardinality is high,
as in your 40K and 60K testing in a 100K rows table, because an InList
that's 40% or 60% of the whole table is close enough to a full scan that
using a native code set or map test similarly outperforms SQLite's generic
paged B-tree indexes like our Boost.MultiIndex-based indexes.

Of course that's speculation on my part, versus your timed experimentation,
so could well be that I'm wrong. And I'll need to look into this eventually.



You're not wrong - although the UDF-timings in my previous post are
correct - it is true that they will remain (relatively) constant -
even in case we reduce the Count in the CompareList from 4 to
1000 or 100.

All timings with 10 records in the "real" table - FullTable-scan
due to using an UDF with a sorting-Dictionary-instance:
36msec (100 items in the compare-list)
43msec (1000 items in the compare-list)
48msec (1 items in the compare-list)
52msec (4 items in the compare-list)

The above was no surprise to me, because I'd expected that due to the
FullTable-scans in case of the UDF-approach... what came as a surprise
was the kind of "inverse-lookup" the SQLite-optimizer apparently
performs, when an index exists on the "real" table which provides
the Column-value to compare against the "In"-list.

In my large compare-lists (4 and 6) this behaviour didn't
become obvious in the timings whilst with 100 and 1000 items in the
compare-lists there was clearly a difference.

Again, all timings with 10 records in the "real" table -
the compare-list created beforehand in a tmp-table -
the table- and index-creation not included in the timings
SQL: Select Count(*) from T Where Col in Tmp

No indexes in the whole setup (not on the "real" table T and also
not on the Tmp-Table):
37msec (100 items in the compare-list)
47msec (1000 items in the compare-list)
84msec (1 items in the compare-list)
136msec (4 items in the compare-list)

With only an index on the Tmp-Table-Column:
37msec (100 items in the compare-list)
56msec (1000 items in the compare-list)..triple-checked, not an outlier
65msec (1 items in the compare-list)
77msec (4 items in the compare-list)

With only an index on the real table (on the compare-value-column):
0.4msec (100 items in the compare-list)
1.9msec (1000 items in the compare-list)
26msec (1 items in the compare-list)
116msec (4 items in the compare-list)

With both indexes (on the real table and the tmp-table):
Identical timings to the case above - apparently the index on
the real table was choosen in favour of the tmp-table-index -
which is the correct choice of the optimizer for all compare-list-counts 
below 3 or so (since with 4 the index

on the tmp-table performs clearly faster already).

So, my mistake was to choose too large compare-list-counts in
my first test-setup - otherwise it would have become obvious
that indexes on the original "real" table are indeed worthwhile.

This holds true for compare-listcounts smaller than about
a third of the total records in the original table.
An index on the Tmp-Table which holds the compare-list is
apparently only worthwhile above this compare-count.

The timings against a 10-records-table in a fulltable-
scan with the UDF (here again - this was on a intel i5 2.8GHz):
36msec (100 items in the compare-list)
43msec (1000 items in the compare-list)
48msec (1 items in the compare-list)
52msec (4 items in the compare-list)

are not that bad - and I'd guess (since the COM-SQLite-wrapper
I've used has some more overhead due to the Interface-delegation)
that there's perhaps 5msec to subtract compared with C/C++ UDFs -
and I can also imagine, that a nice Boost-Object can also out-
perform the SortingDictionary I've used (perhaps by 20-40% or so).

So, in a C/C++ setup I'd expect these values for a UDF
with a Boost-object for the exists-checks (rough estimate):
21msec (100 items in the compare-list)
26msec (1000 items in the 

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

On 01.11.2013 22:04, Alek Paunov wrote:

After reading the whole tread I suspect that you have already considered
the whole thing about the :memory: DB bridging the GUI with the real DB
but I am curious why?


Sorry - unfinished sentence: ... why you have rejected this approach?

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

Hi Dominique,

On 16.10.2013 11:40, Dominique Devienne wrote:

We have an SQLite virtual-table heavy application with a lot of the GUI
driven by SQL queries, and often times we have queries of the form



...



create table t (name text, type text, primary key (name, type));
select * from t where type in (%1);



...



If I somehow missed a better work-around to this lack of array-binding, I'm
also interested of course, but obviously I'd prefer real array binding.



I am thinking about a sort of workaround:

attach ':memory:' as gui

create table gui.node(node int primary key, parent int, value);
/* Model for data binding elements with single value - one tree per 
widget */


create table gui.node_value(node int, typecode int, value);
/* Model for widget elements with multiple values */

create table gui.widget_binding(widget primary key, node int);
/* Current widget binding */

Or more direct alternative:

create table gui.t_based_combo(widget int primary key, label, type text);

Let see the later (for the sake of simplicity)

Variant 1: Ideally you are able to rebind your widgetkit to the inmemory 
gui DB (replacing your current memory containers). Then we have:


- Populating the widget data: insert into gui.t_based_combo select 
$widget, 
- Destroying the widget: delete from gui.t_based_combo where widget = 
$widget
- Using widget: select * from t where type in (select type from 
gui.t_based_combo where widget = $widget)


Variant 2: You are not able (to rebind): Basically the same as Variant 
1, but you have to inject triggers in your memory containers to keep 
them in sync with the gui DB. In this case probably the more general 
model scheme (the first one - "node" tree) will be appropriate, because 
you will likely implement the triggers in some base widget class.


After reading the whole tread I suspect that you have already considered 
the whole thing about the :memory: DB bridging the GUI with the real DB 
but I am curious why?


Kind Regards,
Alek


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Feature Request: Binding Arrays

[Alek Paunov]

On 31.10.2013 18:37, Nico Williams wrote:

On Wed, Oct 16, 2013 at 07:28:04AM -0400, Richard Hipp wrote:

Please see http://www.sqlite.org/src/artifact/2ece66438?ln=13-75


I've been thinking for a while -ever since I happened upon jq(1)- that a
marriage of jq and SQLite3 would be wonderful.

jq is a JSON query language.  It's a functional language.

In my mind this would consist of:

  - a jq function for sqlite3
  - a jq array grouping aggregate function for sqlite3
  - a jq virtual table for sqlite3 (for, e.g., disaggregating values)
  - a jq binding for sqlite3 (so SQLite3 can be invoked from jq)

The IN array binding could then be handled like this:

sqlite3> SELECT * FROM foo WHERE jq('contains($arg1), :in_list, column1);

The value bound to :in_list would be a JSON array or object (faster for
larger sets) of values.


I am sure, there are many SQLite users waiting with hope :-) for an 
extension handling semi-structured data.


BTW, I think some functionality are already online trough 
libspatialite's VirtualXPath virtual table [1].


Might be some code reuse could be possible for the JSON case.

Are there enough interest for something like informal SIG about 
Tree/Graph data processing in SQLite?


Kind Regards,
Alek

[1] 
https://www.gaia-gis.it/fossil/libspatialite/wiki?name=VirtualXPath-intro


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax of EXPLAIN QUERY PLAN "detail" column

[Constantine Yannakopoulos]

On Fri, Nov 1, 2013 at 3:51 PM, Richard Hipp  wrote:

>  I don't think anybody knows.  The EXPLAIN QUERY PLAN syntax is not
> formally
> designed.  It is intended for human reading, not machine parsing.  It
> changes from time to time and is not considered part of the SQLite
> interface.
>
If you want to know all possibilities of what the current code can generate
> for EXPLAIN QUERY PLAN, you'll need to look at the source code and figure
> that out for yourself.  All of the EXPLAIN QUERY PLAN generating code is
> found in the "where.c" source file.  If it were me, I'd probably start
> looking here:  http://www.sqlite.org/src/artifact/f18400f121fd?ln=3049
>

Thank you.

I was afraid that my post might be taken as a request for someone else to
do my homework. I already tried what you suggest but
I don't know C -I am using sqlite3.dll from Delphi - and it was hard for me
to find my way in unfamiliar code in a language I do not understand, but
since you have localized it for me I'll try to read the code and
find the answer myself.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Syntax of EXPLAIN QUERY PLAN "detail" column

[Richard Hipp]

On Fri, Nov 1, 2013 at 9:38 AM, Constantine Yannakopoulos <
alfasud...@gmail.com> wrote:

>
> Could
> someone (probably an SQLite developer) tell me if this grammar covers all
> cases of "detail" values or if there are cases I have missed? Maybe the
> NGQP has introduced some changes I have missed?
>

I don't think anybody knows.  The EXPLAIN QUERY PLAN syntax is not formally
designed.  It is intended for human reading, not machine parsing.  It
changes from time to time and is not considered part of the SQLite
interface.

If you want to know all possibilities of what the current code can generate
for EXPLAIN QUERY PLAN, you'll need to look at the source code and figure
that out for yourself.  All of the EXPLAIN QUERY PLAN generating code is
found in the "where.c" source file.  If it were me, I'd probably start
looking here:  http://www.sqlite.org/src/artifact/f18400f121fd?ln=3049


-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Syntax of EXPLAIN QUERY PLAN "detail" column

[Constantine Yannakopoulos]

Hello all,

I am trying to write a utility that processes the results of EXPLAIN QUERY
PLAN statements and presents them in a graphical manner to developers. I
need to extract information from the "detail" column of the returned result
set (e.g. table name, index name, estimated rows etc.) and since the detail
is somewhat verbose I decided to write a small parser to parse it. After
some examination I have come up with the following EBNF:

plan_statement :=
  ( scan |
search |
aggregate |
compound |
execute |
use )

scan ::=
  "SCAN" ( "TABLE" ObjectName | "SUBQUERY" int ) ["AS" identifier] [ using
] [ cost ]

search ::=
  "SEARCH" ( "TABLE" ObjectName | "SUBQUERY" int) ["AS" identifier] [ using
] [ filter_expr ] [ cost ]

aggregate ::=
  "USE TEMP B-TREE FOR" aggregate_operation

compound ::=
  "COMPOUND SUBQUERIES" int "AND" int SET_OPERATION

execute ::=
  "EXECUTE" ("LIST" | "CORRELATED SCALAR") "SUBQUERY" int

using ::=
  "USING" (
 "AUTOMATIC" ["COVERING"] "INDEX" |
 ["COVERING"] "INDEX" ObjectName |
 "INTEGER PRIMARY KEY"
)

cost ::=
  "(~" int "rows)"

aggregate_operation ::=
  ("GROUP BY" | "ORDER BY")

filter_expr ::=
  "(" ColumnName "=" "?" ["AND" ColumnName "=" "?"]* ")"

set_operation ::= ( "UNION" ["ALL"] | "INTERSECT" | "MINUS" | "EXCEPT" )

use ::=
  "USE" ["TEMP"] "B-TREE" FOR ( "GROUP BY" | "ORDER BY" )

ColumnName ::=
  identifier

ObjectName ::=
  identifier

where sequences are separated by spaces, literals are quoted, production
names are unquoted idents, ( ... | ... ) denote choice groups and [ ... ]
denote optional sequences. Productions "identifier" and "int" are omitted
as trivial.

So far this grammar covers plans of all statements I have examined. Could
someone (probably an SQLite developer) tell me if this grammar covers all
cases of "detail" values or if there are cases I have missed? Maybe the
NGQP has introduced some changes I have missed?

Also, regarding object names, do they include database names if
tables/indexes from attached databases are used in the query? (ObjectName
::= [ identifier '.' ]  identifier instead of ObjectName ::= identifier)

Thank you in advance.

--
Constantine
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[RSmith]

Thanks for all comments & suggestion.

My Point :

If we have trigger creation on per table basis then why can't we have dropping 
also on table basis.

It is not good to have feature where each table owner are free to allow the way 
he want to create trigger.


I can't quite decipher what you mean - sorry.  Are you wanting to block certain table owners from creating some sorts of triggers?  
In SQLite the "Table-owner" has no significant meaning. If you want to do any such blocking it will have to be in your code, which 
can then be extended to simply check if triggers exist or to allow/disallow them.


Or maybe you mean something different?

Either way, just to cover all bases, here's some basic SQL to check triggers in SQlite - I'm sure you know this already, but just to 
be sure there are no misconceptions:


Find all Triggers in Database:
SELECT name FROM sqlite_master WHERE type="trigger"

Find the Triggers for any specific Table:
SELECT name FROM sqlite_master WHERE type="trigger" AND tbl_name="xxxTable"

Find which Table a Trigger belongs to:
SELECT table FROM sqlite_master WHERE type="trigger" AND name="xxxTrigger"

Find if a specific Trigger exists
SELECT Count() FROM sqlite_master WHERE type="trigger" AND name="xxxTrigger"

You can easliy expand these to check for temp triggers or triggers in other 
attached database files etc.

(Where anything with xxx in it obviously means your own name for that thing...)


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inter Process Communincation using SQLite3 Trigger

[techi eth]

Thanks for answer.

I am in sync with your answer.

I believe their is no risk from SQLite3 point of view to handle the
mentioned case.


On Fri, Nov 1, 2013 at 6:47 PM, Igor Tandetnik  wrote:

> On 11/1/2013 5:58 AM, techi eth wrote:
>
>> 1)  Is process – B will allow to Insert?
>>
>
> Sure, why not.
>
>
>  2)  If allowed then it will select notify function from process – B or
>> Process - A?
>>
>
> The custom function will be called in process B - the process doing the
> insertion.
>
>
>  3)  If all works then what risk I will carry? (Like deleting row,
>> deleting trigger will crate any issue?)
>>
>
> I'm not sure I understand the question. Deleting a row will delete a row.
> Deleting a trigger will delete a trigger. Whether those are "issues" in
> your design is for you to decide.
>
>
>  I assume answer will valid for other operation like update & delete
>> operation as well.
>>
>
> Yes, nothing changes if you replace AFTER INSERT with AFTER UPDATE or
> AFTER DELETE.
> --
> Igor Tandetnik
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[Simon Slavin]

On 1 Nov 2013, at 1:15pm, techi eth  wrote:

> If we have trigger creation on per table basis then why can't we have
> dropping also on table basis.

TRIGGERs are not related to just one table.  You can have a TRIGGER which 
mentions any number of tables.

> It is not good to have feature where each table owner are free to allow the
> way he want to create trigger.

Tables do not have owners.  Databases have owners: whoever owns the database 
file.  If you don't want someone to change your database, don't give them write 
access to the database file.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[Stephan Beal]

On Fri, Nov 1, 2013 at 2:15 PM, techi eth  wrote:

> It is not good to have feature where each table owner are free to allow the
> way he want to create trigger.
>

sqlite does not offer per-table owners, nor any concept of "owners" at all,
as is clearly stated in the docs:

http://sqlite.org/zeroconf.html

So (again) you seem to be complaining about something which is "broken"
even though sqlite has never advertised itself as having any such feature.
If you want multi-user support, use a multi-user database. There are plenty
of them out there, but sqlite is not one of them.

The concept you're describing here - multiple "owners" within one database
instance - falls far outside of sqlite's features. Either program them
yourself or use db software which supports them. If you choose to program
them yourself, you will get MUCH better responses on this list if you will
start demonstrating that you've read the available documentation, rather
than showing us your hypothetical (obviously untested) SQL which in no way
reflects the realities of SQL nor sqlite3.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Storing sorted row order in a table

[Simon Slavin]

On 1 Nov 2013, at 4:54am, Igor Tandetnik  wrote:

> There is, but you probably won't like it.
> 
> update myTable set currentOrder = 1 +
>   (select count(*) from myTable t2 where [t2 row comes before myTable row 
> according to order details]);

You're right.  I don't like it.  But I have nothing better.  Drat.

> It would likely be much faster to do it in the application code. Iterate over 
> the SELECT you've shown, and run
> 
> update myTable set currentOrder = :nextValue where rowid=:currentRow;

So it would be reasonable to do something like

SELECT group_concat(rowid) FROM myTable ORDER BY [whatever]

then I could iterate through the values returned as you write.  That seems to 
be the best I could come up with, but if anyone has a faster or more elegant 
solution I'd love to see it.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Inter Process Communincation using SQLite3 Trigger

[Igor Tandetnik]

On 11/1/2013 5:58 AM, techi eth wrote:

1)  Is process – B will allow to Insert?


Sure, why not.


2)  If allowed then it will select notify function from process – B or
Process - A?


The custom function will be called in process B - the process doing the 
insertion.



3)  If all works then what risk I will carry? (Like deleting row,
deleting trigger will crate any issue?)


I'm not sure I understand the question. Deleting a row will delete a 
row. Deleting a trigger will delete a trigger. Whether those are 
"issues" in your design is for you to decide.



I assume answer will valid for other operation like update & delete
operation as well.


Yes, nothing changes if you replace AFTER INSERT with AFTER UPDATE or 
AFTER DELETE.

--
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] TRIGGER syntax

[Simon Slavin]

On 1 Nov 2013, at 4:59am, Igor Korot  wrote:

> I'm not sure I understand.
> Are you saying that I don't have to populate the rank in the table on 
> insertion?

Yes.

>> Given your description above, why try to get all the ranks right every time 
>> you do an INSERT ?
> 
> Because right after insert I am displaying those records and the rank
> field is the second field in the sorting algorithm.
> So, on creating the league I do an insert and then do a sorting and
> display all those records.

Do your insert, making sure you put the player's value into a playersValue 
column.  Then

SELECT * FROM myTable ORDER BY playersValue DESC

Then number the rows in the order they're retrieved.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[techi eth]

Thanks for all comments & suggestion.

My Point :

If we have trigger creation on per table basis then why can't we have
dropping also on table basis.

It is not good to have feature where each table owner are free to allow the
way he want to create trigger.



On Fri, Nov 1, 2013 at 6:33 PM, RSmith  wrote:

> On 2013/11/01 14:41, techi eth wrote:
>
>> I think so checking possible cases of any sqlite operation should not be
>> called as bogus. Where ever I am not sure I will ask expert help & I have
>> got all the time right answers.
>>
> My point here is (and was) that the SQL you said that you have tried, is
> completely bogus, it exists not in any form of SQL server in this World. I
> did not say that you are asking a bogus question - and the point of that
> being that you cannot simply make up SQL statements... at least try
> something that exists in reality and then maybe we can help if something is
> wrong with that.
>
>  I am checking the case where database tables are created by different
>> application. I am sure different application may not check available
>> trigger name in all tables before creating new trigger//...
>>
> They might not check yes, but if they don't, they will get errors - You
> cannot create the same trigger name more than once on any SQL table - and
> as far as I know, in SQLIte specifically, you cannot even have the same
> trigger name for different tables within the same database - so it is
> impossible to create the scenario for which you want to test with your code.
>
>
>  *Question:*
>>
>>
>> What happen when same trigger name is used by two different tables in
>> database and drop trigger query get executed.Find below statement :
>>
>> CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl1 BEGIN SELECT
>> Trigger_notifier_function();**END
>>
>>
>> CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl2 BEGIN SELECT
>> Trigger_notifier_function1();**END
>>
>> DROP TRIGGER IF EXISTS TestTrigger
>>
>> Please correct me if my question is wrong.
>> Note: Trigger logic will be different but trigger names are same.
>>
> This is what I mean with bogus SQL... have you actually tried to do that
> in SQLite?  You cannot do it, the second trigger will never be created and
> therefore never be possible to exist, and therefore unnecessary to check
> for. Check it for yourself!
>
>
>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[RSmith]

On 2013/11/01 14:41, techi eth wrote:

I think so checking possible cases of any sqlite operation should not be
called as bogus. Where ever I am not sure I will ask expert help & I have
got all the time right answers.
My point here is (and was) that the SQL you said that you have tried, is completely bogus, it exists not in any form of SQL server 
in this World. I did not say that you are asking a bogus question - and the point of that being that you cannot simply make up SQL 
statements... at least try something that exists in reality and then maybe we can help if something is wrong with that.



I am checking the case where database tables are created by different
application. I am sure different application may not check available
trigger name in all tables before creating new trigger//...
They might not check yes, but if they don't, they will get errors - You cannot create the same trigger name more than once on any 
SQL table - and as far as I know, in SQLIte specifically, you cannot even have the same trigger name for different tables within the 
same database - so it is impossible to create the scenario for which you want to test with your code.




*Question:*

What happen when same trigger name is used by two different tables in
database and drop trigger query get executed.Find below statement :

CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl1 BEGIN SELECT
Trigger_notifier_function();END


CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl2 BEGIN SELECT
Trigger_notifier_function1();END

DROP TRIGGER IF EXISTS TestTrigger

Please correct me if my question is wrong.
Note: Trigger logic will be different but trigger names are same.
This is what I mean with bogus SQL... have you actually tried to do that in SQLite?  You cannot do it, the second trigger will never 
be created and therefore never be possible to exist, and therefore unnecessary to check for. Check it for yourself!




___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[Stephan Beal]

On Fri, Nov 1, 2013 at 1:41 PM, techi eth  wrote:

> Note: Trigger logic will be different but trigger names are same.
>

No, they won't, because you'll get an error when creating the second
trigger with the same name. There is no per-table namespace for triggers.
It is possible (at least syntactically) to have the same trigger name in
two databases (attached to each other) but not two _different_ triggers
with the same name in the same db, as the documentation's diagrams clearly
imply:

http://www.sqlite.org/lang_createtrigger.html

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[techi eth]

You seem to have a habit of inventing bogus SQL and then, when it doesn't
work, ask for the correct form on here.  If I was the forum police I would
suggest reading some freely available on-line documents, but since I'm not.

I think so checking possible cases of any sqlite operation should not be
called as bogus. Where ever I am not sure I will ask expert help & I have
got all the time right answers.
With my politeness I do not want to discuss this topic here. If you have
any specific concern please send mail separately to me I will reply
accordingly.

SQL is not a flow-control language, it makes adjustments to (or extractions
from) datasets based on relational parameters, some of which makes it seem
very very clever (and indeed I contend that it is quite clever), but it
still isn't a flow-control specification.

This is Ok. I understood & used same for test.DROP TRIGGER IF EXISTS
trigger_name

If I may ask, how on Earth did you end up having a Query whereby the
creation (or deletion) of entire Triggers becomes part of the

data-execution-logic?  Maybe explain the first principle you wish to
achieve and we could suggest how to best get there (as many

have done here already).

*Let me explain:*

I am checking the case where database tables are created by different
application. I am sure different application may not check available
trigger name in all tables before creating new trigger.

*Question:*

What happen when same trigger name is used by two different tables in
database and drop trigger query get executed.Find below statement :

CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl1 BEGIN SELECT
Trigger_notifier_function();END


CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl2 BEGIN SELECT
Trigger_notifier_function1();END

DROP TRIGGER IF EXISTS TestTrigger

Please correct me if my question is wrong.
Note: Trigger logic will be different but trigger names are same.



On Fri, Nov 1, 2013 at 5:36 PM, RSmith  wrote:

> You seem to have a habit of inventing bogus SQL and then, when it doesn't
> work, ask for the correct form on here.  If I was the forum police I would
> suggest reading some freely available on-line documents, but since I'm not
> - here's an attempt at answering the question:
>
> SQL is not a flow-control language, it makes adjustments to (or
> extractions from) datasets based on relational parameters, some of which
> makes it seem very very clever (and indeed I contend that it is quite
> clever), but it still isn't a flow-control specification.
>
> You will need to decide in your program code whether a Trigger or Index or
> Table needs to exist or not, and from there instuct SQL to make it or drop
> it.
>
> If I may ask, how on Earth did you end up having a Query whereby the
> creation (or deletion) of entire Triggers becomes part of the
> data-execution-logic?  Maybe explain the first principle you wish to
> achieve and we could suggest how to best get there (as many have done here
> already).
>
>
>
>
> On 2013/11/01 13:53, techi eth wrote:
>
>> How to drop trigger from specific table with condition around?
>>
>>
>>
>> I tried this but got syntax error.
>>
>> DROP TRIGGER trigger_name  From tbl_name = TestTbl WHERE ;
>>
>> Thanks
>> __**_
>> sqlite-users mailing list
>> sqlite-users@sqlite.org
>> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>>
>
> __**_
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-**bin/mailman/listinfo/sqlite-**users
>
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[RSmith]

You seem to have a habit of inventing bogus SQL and then, when it doesn't work, ask for the correct form on here.  If I was the 
forum police I would suggest reading some freely available on-line documents, but since I'm not - here's an attempt at answering the 
question:


SQL is not a flow-control language, it makes adjustments to (or extractions from) datasets based on relational parameters, some of 
which makes it seem very very clever (and indeed I contend that it is quite clever), but it still isn't a flow-control specification.


You will need to decide in your program code whether a Trigger or Index or Table needs to exist or not, and from there instuct SQL 
to make it or drop it.


If I may ask, how on Earth did you end up having a Query whereby the creation (or deletion) of entire Triggers becomes part of the 
data-execution-logic?  Maybe explain the first principle you wish to achieve and we could suggest how to best get there (as many 
have done here already).




On 2013/11/01 13:53, techi eth wrote:

How to drop trigger from specific table with condition around?



I tried this but got syntax error.

DROP TRIGGER trigger_name  From tbl_name = TestTbl WHERE ;

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[Daniel Polski]

I don't understand what condition you might be talking about.

As far as I know the functionality only allows you to simply drop a 
trigger, in your case:


DROP TRIGGER trigger_name;

or:

DROP TRIGGER IF EXISTS trigger_name;

Best regards,
Daniel


techi eth skrev 2013-11-01 12:53:

How to drop trigger from specific table with condition around?



I tried this but got syntax error.

DROP TRIGGER trigger_name  From tbl_name = TestTbl WHERE ;

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Dropping Trigger from Table

[Stephan Beal]

On Fri, Nov 1, 2013 at 12:53 PM, techi eth  wrote:

> DROP TRIGGER trigger_name  From tbl_name = TestTbl WHERE ;
>

Google "drop trigger sqlite" says:

http://www.sqlite.org/lang_droptrigger.html

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Since tyranny's the only guaranteed byproduct of those who insist on a
perfect world, freedom will have to do." -- Bigby Wolf
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Dropping Trigger from Table

[techi eth]

How to drop trigger from specific table with condition around?



I tried this but got syntax error.

DROP TRIGGER trigger_name  From tbl_name = TestTbl WHERE ;

Thanks
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Inter Process Communincation using SQLite3 Trigger

[techi eth]

Hi,



I understand SQLite3 will not be able to handle any IPC related operation.
I am attempting my idea below to handle IPC with the help of SQLite3
Trigger.



*Scenario:*

I have two processes. Process – A will be responsible to create trigger &
Process -B will doing operation like update, insert or delete. Once process
– B will do update, Insert or delete, process – A will get notification
automatically.



1) Process - A create trigger where it actually select a function in
trigger action.

CREATE TRIGGER TestTrigger AFTER INSERT ON TestTbl

BEGIN

SELECT Trigger_notifier_function();

END

Note: Process - A will register function using sqlite_create_function().Mostly
Trigger_notifier_function() is dummy here.



2) Process - B will also create same name function using
sqlite_create_function().This function have some logic to handle IPC.

3) Process – B will insert data in table.



*My question:*

1)  Is process – B will allow to Insert?

2)  If allowed then it will select notify function from process – B or
Process - A?

3)  If all works then what risk I will carry? (Like deleting row,
deleting trigger will crate any issue?)



I assume answer will valid for other operation like update & delete
operation as well.



Thanks.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users