Re: [sqlite] Sqlite handler in trigger

[Igor Tandetnik]

On 9/6/2013 12:51 AM, techi eth wrote:

I am not sure if my problem I have stated clearly, found below detail
explanation!!!



Process 1:

Handler1 = OpenConn();

Sqlite_Createfunc(Handler1, my_notifier_function()..);

CREATE TRIGGER Event_test1 AFTER Update ON test BEGIN

SELECT my_notifier_function();

END;



Proecss2:

Handler2 = OpenConn();

Update test SET value;


That last statement will fail with "unknown function 
'my_notifier_function' " error.



In this scenario when proecss2 will do update...


In this scenario process2 will be unable to do update.


If above will not happen then please suggest what is best I can get from
sqlite3 to handle this kind of scenario.


Once again: there is no mechanism built into SQLite that would allow one 
process to be automatically notified that another process made a change 
to the database. Which part of this sentence did you find unclear the 
first time round?

--
Igor Tandetnik

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

Re: [sqlite] Sqlite handler in trigger

[techi eth]

I am not sure if my problem I have stated clearly, found below detail
explanation!!!



Process 1:

Handler1 = OpenConn();

Sqlite_Createfunc(Handler1, my_notifier_function()..);

CREATE TRIGGER Event_test1 AFTER Update ON test BEGIN

   SELECT my_notifier_function();

END;



Proecss2:

Handler2 = OpenConn();

Update test SET value;



In this scenario when proecss2 will do update then trigger logic will get
executed & my_notifier_function() function may get called.

If above will not happen then please suggest what is best I can get from
sqlite3 to handle this kind of scenario. May be any other possible
operation?

Thanks..



On Thu, Sep 5, 2013 at 6:55 PM, Igor Tandetnik  wrote:

> On 9/5/2013 5:39 AM, techi eth wrote:
>
>> I have case where one process is updating data with his private handler
>> but
>> trigger on update is created by other process by his private handler.
>>
>
> I'm not sure I understand this sentence. When you run CREATE TRIGGER
> statement, the trigger you've just created becomes part of the database
> schema, visible to all connections (unless you do CREATE TEMP TRIGGER; is
> this what you are talking about?)
>
> On the other hand, sqlite3_create_function() registers a custom function
> just for this connection. If you create a trigger that calls this function,
> then those connections where the function is not registered will fail to
> even prepare an UPDATE statement - SQLite will issue an "unknown function"
> error.
>
> In general, there is no mechanism built into SQLite that would allow one
> process to be automatically notified that another process made a change to
> the database. If that's what you want, you would have to implement that in
> your application - you can't somehow trick SQLite into doing it for you.
> --
> 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] UPDATE question

[James K. Lowden]

On Thu, 5 Sep 2013 19:53:15 +0100
Simon Slavin  wrote:

> On 5 Sep 2013, at 7:20pm, Peter Haworth  wrote:
> 
> > That works fine but wondering if there might be a single UPDATE
> > statement that could do this for me.  I can use the WHERE clause to
> > select sequence 3,4, and 5 but  the UPDATE has to process the rows
> > in descending sequence order to avoid UNIQUE conflicts.
> 
> An interesting point.  If all the updates are done inside a COMMIT,
> then the conflict may not be recognised because by the time the first
> change is written back to the table, the conflicting entry has
> already been renumbered.

I was puzzled by this thread, so ran my own little test.  To my dismay,
UPDATE is not atomic in SQLite.  

$ rm -f db; sqlite3 -echo db < sql
create table t 
( PKey INTEGER PRIMARY KEY
, Name TEXT
, Sequence INTEGER
, unique (Name, Sequence)
);

insert into t (Name, Sequence) values ('Blue', 1);
insert into t (Name, Sequence) values ('Blue', 2);
insert into t (Name, Sequence) values ('Blue', 3);
insert into t (Name, Sequence) values ('Blue', 4);
insert into t (Name, Sequence) values ('Blue', 5);

-- insert a new 3

BEGIN TRANSACTION ;
UPDATE t 
SET Sequence = Sequence + 1 
WHERE Sequence >= 3 
AND Name = 'Blue';
SQL error near line 17: columns Name, Sequence are not unique

That's perfectly good SQL.  SQLite is simply not executing the
update atomically.  

Anyone tempted to protest may be forgetting "atomic" means more than
"all or nothing". It also means the DBMS may execute the transaction
however it sees fit, at the cost of ensuring that constraints remain in
effect upon commit but *only* upon commit. That's why this works:

sqlite> create table a (a int, b int check( a + b = 3));
sqlite> insert into a values (1, 2);
sqlite> select * from a;
a   b 
--  --
1   2 
sqlite> update a set a = b, b = a;
sqlite> select * from a;
a   b 
--  --
2   1 

Constraints hold for the whole database at all times whenever the user
can see the data.  Any update is valid if it can logically be applied
and leave the database in a state consistent with its declared
constraints.  

Here's how Marc L. Allen's query should work (different
DBMS):

$ bsqldb  < /tmp/sql 
   PKey  Name   Sequence
---  --  ---
  1  Blue  1
  2  Blue  2
  3  Blue  4
  4  Blue  5
  5  Blue  6
  6  Blue  3
6 rows affected

Note that the final insert is assigned the next auto-generated 
PKey (6), and the old 3 is now 4, etc.  

Granted, it's not easy.  The update processing cannot simply find each
row in turn and update it (as appears to be the case now). One
alternative would be to make a copy of the whole set, update it, and
then apply it en masse.  Another probably slower way would be to "keep
trying": if a row can't be updated, find one that can and continue
until they're all done or you're painted into a corner and
have to rollback.  Doubtless there are better ways.  

No one ever said a DBMS was a simple creature!  

Simon mentioned 

> http://www.sqlite.org/conflict.html

This has nothing to do with deferred constraint resolution.  Deferred
constraint resolution involves more than one table being updated in a
transaction, deferring constraint enforcement until the
commit.  See for example this bit of DB2 documentation, 
http://pic.dhe.ibm.com/infocenter/idshelp/v115/index.jsp?topic=%2Fcom.ibm.sqls.doc%2Fids_sqs_0633.htm.
  

I also looked at http://www.sqlite.org/lang_conflict.html, which says, 

"REPLACE
When a UNIQUE constraint violation occurs, the REPLACE algorithm
deletes pre-existing rows that are causing the constraint violation
prior to inserting or updating the current row and the command
continues executing normally."

The order in which the rows are (internally) processed is arbitrary,
and the results of using "or replace" here are predictably
unpredictable:

BEGIN TRANSACTION ;
UPDATE or replace t 
SET Sequence = Sequence + 1 
WHERE Sequence >= 3 
AND Name = 'Blue';

insert into t (Name, Sequence) values ('Blue', 3);
COMMIT;

select * from t;
PKeyNameSequence  
--  --  --
1   Blue1 
2   Blue2 
3   Blue4 
5   Blue6 
6   Blue3 

but what's a Blue 5 among friends?  :-/  

As regards the documentation, the only fair thing to do for now would
be to add UPDATE to the omitted page, explaining the row-by-rowism.  
I would also suggest flagging the UPDATE page itself, because the
behavior is very much at 

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 9:45 PM, Yuzem  wrote:

> It is incredibly fast but it gives me the wrong result:

Yeah… you forgot the 'where' clause in the subquery… you need to restrict it on 
the genre 


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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Thanks Petite Abeille, I translated your code to this:
SELECT genres.genres,
   (
SELECT  substr(group_concat(name,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres
  ONmovies.movies = genres.movies

  ORDER BY  movies.name

  LIMIT 6
)
) AS sample

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;
time: 0.028s

It is incredibly fast but it gives me the wrong result:
Action|American Graffiti American Graffiti American Graffiti Mulhol
Adventure|American Graffiti American Graffiti American Graffiti Mulhol
Animation|American Graffiti American Graffiti American Graffiti Mulhol
Biography|American Graffiti American Graffiti American Graffiti Mulhol
Etc...

Am I doing something wrong?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71035.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] SQL insert performance on Windows Mobile 6.5

[Bullock, Tony]

Richard, Thanks for the reply.

  I will look into the profiling tools.  The only diagnostic I have is the CPU 
utilization of the java process which is very high (90%) for the database 
update.

-Tony

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, September 05, 2013 4:51 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] SQL insert performance on Windows Mobile 6.5

On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony wrote:

>
> Hi,
>
>   I have a performance issue with SQLitejdbc with Java on Windows
> Mobile
> 6.5 running on a MC9190-G mobile computer.  I am using
> sqlitejdbc-v056.jar with the NSIcom CrE-ME 4.12 JVM.
>
>   Performance of the insert is very low.  I am getting about 1000
> records added a minute.  Any ideas the on how to improve the
> performance would be appreciated.
>

On a workstation, with PRAGMA synchronous=OFF, you should be getting about
300,000 rows per *second*.  Performance on your mobile computer will be less, 
of course, but it shouldn't be 18,000 times less.

Do you have any profiling tools available to see what it taking so long?


>
> Code overview:
>
>
> 1.   Query an iSeries database with the JTOpen package to pull 56,000
> records.   This is fairly quick, less than 10 seconds to complete.
>
> 2.   For each record returned, create an insert batch statement.
>
> 3.   When 1000 records are added to the batch, execute the batch
> update and commit the transaction (autocommit is disabled)
>
> 4.   Two pragma statements are also used when the connection is
> created.
>
> a.   PRAGMA journal_mode=OFF;
>
> b.  PRAGMA synchronous = OFF;
>
>   The key sections of code:
>
> /*
> * PopulateItems.java
> *
> * Inserts items from the iSeries ERP into the PDA database item file
> *
> */
>
> package com.probuild.pda;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.text.SimpleDateFormat;
> import java.util.Date;
>
> /**
> *
> */
> public class PopulateItems {
>
> private Connection iSeriesConn;
> private Connection sqliteConn;
> public final int DB_COMMIT_COUNT = 5000;
>
> /**
> * Creates a new instance of PopulateItems
> */
> public PopulateItems() {
>
> // Establish connections to iSeries and SQLite DBs
> try {
>
> Class.forName( "com.ibm.as400.access.AS400JDBCDriver" );
> String url ="jdbc:as400://as400.dxlbr.com
> ;S10036FD;naming=sql;errors=full;";
> iSeriesConn = DriverManager.getConnection( url, "SATCP",
> "SATCP" );
>
>
> Class.forName( "org.sqlite.JDBC" );
> sqliteConn =
> DriverManager.getConnection("jdbc:sqlite:\\pda.db");
> Statement statement = sqliteConn.createStatement();
>
> // turn journaling off
> String sql = "PRAGMA journal_mode=OFF;";
> statement.execute(sql);
>
> // turn journaling off
> sql = "PRAGMA synchronous = OFF;";
> statement.execute(sql);
>
> statement.close();
>
> sqliteConn.setAutoCommit(false);
> }
> catch (Exception e) {
> System.out.println ( e.getMessage() );
> }
> }
>
>
> public void closeConnections ( ) {
>
> try {
>
> iSeriesConn.close();
> sqliteConn.close();
> }
> catch ( Exception e ) {
> System.out.println ( e.getMessage() );
> }
>
> }
>
>
> public boolean buildItemTable (String locCode) {
>
> Statement iStmt = null;
> PreparedStatement pStmt = null;
>
> ResultSet iResult = null;
>
> boolean success = false;
>
>
> System.out.println( "Building Item Table..." );
>
> try {
>
> pStmt = sqliteConn.prepareStatement(
> "INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ?, ? )" );
>
> // SKU, UPC, Alt Item, Item Desc, List Unit Of Measure,
> Dept Code, Mfg Item#
> // Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N),
> RISBCD (P or C)
> String sql =
> " SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU,
> RIDEPT, RIMNF#, " +
> " RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD" +
> " FROM DRMS.DRITEMFL " +
> " WHERE RILOC# = " + locCode + " AND RIRLC# = 0 AND
> RISKU#
> > 5 " +
> " ORDER BY RISKU#";
>
> iStmt = iSeriesConn.createStatement();
>
> iResult = iStmt.executeQuery( sql );
>
> System.out.println( "iSeries select form dritemfl
> complete: ");
>
> int count = 0;
>
> while ( iResult.next ( ) ) {
>
> int sku 

Re: [sqlite] SQL insert performance on Windows Mobile 6.5

[Richard Hipp]

On Thu, Sep 5, 2013 at 7:26 PM, Bullock, Tony wrote:

>
> Hi,
>
>   I have a performance issue with SQLitejdbc with Java on Windows Mobile
> 6.5 running on a MC9190-G mobile computer.  I am using sqlitejdbc-v056.jar
> with the NSIcom CrE-ME 4.12 JVM.
>
>   Performance of the insert is very low.  I am getting about 1000 records
> added a minute.  Any ideas the on how to improve the performance would be
> appreciated.
>

On a workstation, with PRAGMA synchronous=OFF, you should be getting about
300,000 rows per *second*.  Performance on your mobile computer will be
less, of course, but it shouldn't be 18,000 times less.

Do you have any profiling tools available to see what it taking so long?


>
> Code overview:
>
>
> 1.   Query an iSeries database with the JTOpen package to pull 56,000
> records.   This is fairly quick, less than 10 seconds to complete.
>
> 2.   For each record returned, create an insert batch statement.
>
> 3.   When 1000 records are added to the batch, execute the batch
> update and commit the transaction (autocommit is disabled)
>
> 4.   Two pragma statements are also used when the connection is
> created.
>
> a.   PRAGMA journal_mode=OFF;
>
> b.  PRAGMA synchronous = OFF;
>
>   The key sections of code:
>
> /*
> * PopulateItems.java
> *
> * Inserts items from the iSeries ERP into the PDA database item file
> *
> */
>
> package com.probuild.pda;
>
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.PreparedStatement;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.text.SimpleDateFormat;
> import java.util.Date;
>
> /**
> *
> */
> public class PopulateItems {
>
> private Connection iSeriesConn;
> private Connection sqliteConn;
> public final int DB_COMMIT_COUNT = 5000;
>
> /**
> * Creates a new instance of PopulateItems
> */
> public PopulateItems() {
>
> // Establish connections to iSeries and SQLite DBs
> try {
>
> Class.forName( "com.ibm.as400.access.AS400JDBCDriver" );
> String url ="jdbc:as400://as400.dxlbr.com
> ;S10036FD;naming=sql;errors=full;";
> iSeriesConn = DriverManager.getConnection( url, "SATCP",
> "SATCP" );
>
>
> Class.forName( "org.sqlite.JDBC" );
> sqliteConn =
> DriverManager.getConnection("jdbc:sqlite:\\pda.db");
> Statement statement = sqliteConn.createStatement();
>
> // turn journaling off
> String sql = "PRAGMA journal_mode=OFF;";
> statement.execute(sql);
>
> // turn journaling off
> sql = "PRAGMA synchronous = OFF;";
> statement.execute(sql);
>
> statement.close();
>
> sqliteConn.setAutoCommit(false);
> }
> catch (Exception e) {
> System.out.println ( e.getMessage() );
> }
> }
>
>
> public void closeConnections ( ) {
>
> try {
>
> iSeriesConn.close();
> sqliteConn.close();
> }
> catch ( Exception e ) {
> System.out.println ( e.getMessage() );
> }
>
> }
>
>
> public boolean buildItemTable (String locCode) {
>
> Statement iStmt = null;
> PreparedStatement pStmt = null;
>
> ResultSet iResult = null;
>
> boolean success = false;
>
>
> System.out.println( "Building Item Table..." );
>
> try {
>
> pStmt = sqliteConn.prepareStatement(
> "INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?,
> ?, ?, ?, ? )" );
>
> // SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept
> Code, Mfg Item#
> // Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N),
> RISBCD (P or C)
> String sql =
> " SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT,
> RIMNF#, " +
> " RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD" +
> " FROM DRMS.DRITEMFL " +
> " WHERE RILOC# = " + locCode + " AND RIRLC# = 0 AND RISKU#
> > 5 " +
> " ORDER BY RISKU#";
>
> iStmt = iSeriesConn.createStatement();
>
> iResult = iStmt.executeQuery( sql );
>
> System.out.println( "iSeries select form dritemfl complete: ");
>
> int count = 0;
>
> while ( iResult.next ( ) ) {
>
> int sku = iResult.getInt( "RISKU#" );
> long upc = iResult.getLong( "RIUPC#" );
> //String sku = iResult.getString( "RISKU#" );
> //String upc = iResult.getString( "RIUPC#" );
> String alt = iResult.getString( "RIAITM" );
> String desc = iResult.getString( "RIDESC" );
> String uom = iResult.getString( "RILSTU" );
> String dept = iResult.getString( "RIDEPT" );
> String mnf = 

[sqlite] SQL insert performance on Windows Mobile 6.5

[Bullock, Tony]

Hi,

  I have a performance issue with SQLitejdbc with Java on Windows Mobile 6.5 
running on a MC9190-G mobile computer.  I am using sqlitejdbc-v056.jar with the 
NSIcom CrE-ME 4.12 JVM.

  Performance of the insert is very low.  I am getting about 1000 records added 
a minute.  Any ideas the on how to improve the performance would be appreciated.

Code overview:


1.   Query an iSeries database with the JTOpen package to pull 56,000 
records.   This is fairly quick, less than 10 seconds to complete.

2.   For each record returned, create an insert batch statement.

3.   When 1000 records are added to the batch, execute the batch update and 
commit the transaction (autocommit is disabled)

4.   Two pragma statements are also used when the connection is created.

a.   PRAGMA journal_mode=OFF;

b.  PRAGMA synchronous = OFF;

  The key sections of code:

/*
* PopulateItems.java
*
* Inserts items from the iSeries ERP into the PDA database item file
*
*/

package com.probuild.pda;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.Date;

/**
*
*/
public class PopulateItems {

private Connection iSeriesConn;
private Connection sqliteConn;
public final int DB_COMMIT_COUNT = 5000;

/**
* Creates a new instance of PopulateItems
*/
public PopulateItems() {

// Establish connections to iSeries and SQLite DBs
try {

Class.forName( "com.ibm.as400.access.AS400JDBCDriver" );
String url 
="jdbc:as400://as400.dxlbr.com;S10036FD;naming=sql;errors=full;";
iSeriesConn = DriverManager.getConnection( url, "SATCP", "SATCP" );


Class.forName( "org.sqlite.JDBC" );
sqliteConn = DriverManager.getConnection("jdbc:sqlite:\\pda.db");
Statement statement = sqliteConn.createStatement();

// turn journaling off
String sql = "PRAGMA journal_mode=OFF;";
statement.execute(sql);

// turn journaling off
sql = "PRAGMA synchronous = OFF;";
statement.execute(sql);

statement.close();

sqliteConn.setAutoCommit(false);
}
catch (Exception e) {
System.out.println ( e.getMessage() );
}
}


public void closeConnections ( ) {

try {

iSeriesConn.close();
sqliteConn.close();
}
catch ( Exception e ) {
System.out.println ( e.getMessage() );
}

}


public boolean buildItemTable (String locCode) {

Statement iStmt = null;
PreparedStatement pStmt = null;

ResultSet iResult = null;

boolean success = false;


System.out.println( "Building Item Table..." );

try {

pStmt = sqliteConn.prepareStatement(
"INSERT INTO item VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 
?, ? )" );

// SKU, UPC, Alt Item, Item Desc, List Unit Of Measure, Dept Code, 
Mfg Item#
// Vnd Item#, Min Quantity, Max Quantity, Net Item (Y/N), RISBCD (P 
or C)
String sql =
" SELECT RISKU#, RIUPC#, RIAITM, RIDESC, RILSTU, RIDEPT, 
RIMNF#, " +
" RIVITM, RIMINQ, RIMAXQ, RINETI, RISBCD" +
" FROM DRMS.DRITEMFL " +
" WHERE RILOC# = " + locCode + " AND RIRLC# = 0 AND RISKU# > 
5 " +
" ORDER BY RISKU#";

iStmt = iSeriesConn.createStatement();

iResult = iStmt.executeQuery( sql );

System.out.println( "iSeries select form dritemfl complete: ");

int count = 0;

while ( iResult.next ( ) ) {

int sku = iResult.getInt( "RISKU#" );
long upc = iResult.getLong( "RIUPC#" );
//String sku = iResult.getString( "RISKU#" );
//String upc = iResult.getString( "RIUPC#" );
String alt = iResult.getString( "RIAITM" );
String desc = iResult.getString( "RIDESC" );
String uom = iResult.getString( "RILSTU" );
String dept = iResult.getString( "RIDEPT" );
String mnf = iResult.getString( "RIMNF#" );

String vendorItem = iResult.getString( "RIVITM" );
int minQ = iResult.getInt( "RIMINQ" );
int maxQ = iResult.getInt( "RIMAXQ" );
String netItem = iResult.getString( "RINETI" );
String subItemCode = iResult.getString( "RISBCD" );

pStmt.setInt( 1, sku );
pStmt.setLong( 2, upc );
pStmt.setString(3, alt.trim() );
pStmt.setString( 4, desc.replace( '\'', ' ').trim() );
pStmt.setString( 5, uom );
pStmt.setString( 6, 

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 11:27 PM, Yuzem  wrote:

> Any clue on why LEFT JOIN is so slow when used with genres but not with
> larger tables?

Sure. But your conclusion is a most likely a red herring. The crux of the 
matter is that inner and outer join have a wildly different semantic. And 
therefore execution plan. That's all. Seems all very reasonable from an 
optimizer point of view.

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Petite Abeille-2 wrote
> Sure. But your conclusion is a most likely a red herring. The crux of the
> matter is that inner and outer join have a wildly different semantic. And
> therefore execution plan. That's all. Seems all very reasonable from an
> optimizer point of view.

But I have no conclusion, I acknowledge that inner and outer join are
different but I don't know why LEFT JOIN works very fast on larger tables
and very slow on smaller tables at least in those cases. I would like to
have a conclusion on that matter.



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71042.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 10:28 PM, Yuzem  wrote:

> Ok, wonderful, now it is working correctly but how do I select multiple
> columns from table movies?
> Should I add another sub query?

Nope. You have now changed the problem definition, so scalars will not be a 
good fit. Blindly copy & paste them will not get you anywhere.

The key to success in your case is to access the movie_genre table only once, 
as selectively as possible. 

So, if, for a given genre you want 4 movies, you could try something along 
these lines:

selectgenre.code,
  movie.title
from  (
selectmovie_genre.movie_id,
  movie_genre.genre_id
from  movie_genre

where movie_genre.genre_id = 30

order by  movie_genre.id

limit 4
  )
asmovie_genre

join  movie
onmovie.id = movie_genre.movie_id

join  genre
ongenre.id = movie_genre.genre_id

Which gives you 4 rows:

Western|"26 Men" (1957)
Western|"A Man Called Shenandoah" (1965)
Western|"ABC Weekend Specials" (1977) {The Winged Colt (#1.5)}
Western|"Action in the Afternoon" (1953)





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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Thanks but 4 rows is not what I am looking for.
I found a solution, concatenation:
SELECT genres.genres,
   (
SELECT  group_concat(movie,' ')
FROM
(
  SELECTmovies.movies||','||name movie
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 3
)
)

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;

Any clue on why LEFT JOIN is so slow when used with genres but not with
larger tables?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71040.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

Ok, wonderful, now it is working correctly but how do I select multiple
columns from table movies?
Should I add another sub query?
Example:

SELECT genres.genres,
   (
SELECT  substr(group_concat(name,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 6
)
),
(
SELECT  substr(group_concat(movies,' '),1,60)
FROM
(
  SELECTname
  FROM  movies

  JOIN  genres AS movie_genres
  ONmovies.movies = movie_genres.movies

  WHERE movie_genres.genres = genres.genres

  ORDER BY  movies.name

  LIMIT 6
)
)

FROM  genres
GROUP BY  genres
ORDER BY  genres.genres;

If I add another sub query it takes almost twice the time than using only
one sub query. It is still faster than before.

 
Petite Abeille-2 wrote
> Yeah… the inner join vs out join will produce a significantly different
> access plan.

Yes but if that's the case don't you think that the difference in time is a
bit much?
And why there is almost no difference between JOIN and LEFT JOIN when using
tasks and people which are larger tables?



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71038.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 5, 2013, at 8:56 PM, Yuzem  wrote:

> SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies
> GROUP BY genres ORDER BY genres;
> time: 2.475s
> 
> SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP
> BY genres ORDER BY genres;
> time: 0.035s

Yeah… the inner join vs out join will produce a significantly different access 
plan.

For example, using a different schema:

(1) inner join
0|0|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1
0|1|0|SEARCH TABLE genre USING INTEGER PRIMARY KEY (rowid=?)

(2) outer join
0|0|0|SCAN TABLE genre USING COVERING INDEX sqlite_autoindex_genre_1
0|1|1|SCAN TABLE movie_genre USING COVERING INDEX sqlite_autoindex_movie_genre_1

In this case, the first plan is much better in terms of throughput as 
movie_genre is much larger table.

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

I did some testing and found some strange results.

SELECT genres FROM genres LEFT JOIN movies ON genres.movies = movies.movies
GROUP BY genres ORDER BY genres;
time: 2.475s

SELECT genres FROM genres JOIN movies ON genres.movies = movies.movies GROUP
BY genres ORDER BY genres;
time: 0.035s

SELECT tasks FROM tasks LEFT JOIN people ON tasks.people = people.people
GROUP BY tasks ORDER BY tasks"
time: 0.164s

SELECT tasks FROM tasks JOIN people ON tasks.people = people.people GROUP BY
tasks ORDER BY tasks"
time: 0.163s

The strange thing is that tasks and people are much larger tables than
genres and movies:
SELECT count() from genres;
3998
SELECT count() from movies;
1529
SELECT count() from tasks;
24964
SELECT count() from people;
19626



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71031.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Simon Slavin]

On 5 Sep 2013, at 7:20pm, Peter Haworth  wrote:

> That works fine but wondering if there might be a single UPDATE statement
> that could do this for me.  I can use the WHERE clause to select sequence
> 3,4, and 5 but  the UPDATE has to process the rows in descending sequence
> order to avoid UNIQUE conflicts.

An interesting point.  If all the updates are done inside a COMMIT, then the 
conflict may not be recognised because by the time the first change is written 
back to the table, the conflicting entry has already been renumbered.

Nope, apparently SQLite doesn't support deferring conflict resolution until the 
COMMIT [1].  Maybe that should be added to these two pages:




Simon.

[1] Except for FOREIGN KEYS.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Yuzem]

I did an ANALYZE but I'm getting the same results.
I do have indexes:
CREATE TABLE movies (
  movies UNIQUE,
  name,
  icon_modified
);

CREATE TABLE genres (
  genres,
  movies,
  UNIQUE(genres,movies)
);

people has an index on people (people UNIQUE) and tasks is a view:
CREATE VIEW tasks AS
SELECT 'creators'  tasks,movies,people,'' details FROM creators
UNION ALL
SELECT 'directors' tasks,movies,people,'' details FROM directors
UNION ALL
SELECT 'writers'   tasks,movies,people,'' details FROM writers
UNION ALL
SELECT 'actors'tasks,movies,people,characters details FROM actors;

The tables creators, directors, etc, have similar indexes as genres:
CREATE TABLE directors (
  directors,
  people,
  UNIQUE(directors,people)
);



--
View this message in context: 
http://sqlite.1065341.n5.nabble.com/Please-help-me-optimize-this-LEFT-JOIN-query-tp71000p71033.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Simon Slavin]

On 5 Sep 2013, at 7:56pm, Yuzem  wrote:

> I did some testing and found some strange results.

Please do an ANALYZE and try the same things again.

Also, do you have any indexes on those tables (apart from the primary keys, of 
course) ?

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

Re: [sqlite] Please help me optimize this LEFT JOIN query.

[Petite Abeille]

On Sep 4, 2013, at 4:21 PM, Yuzem  wrote:

> I want to construct genres icons and each icon must display 4 movies.

Assuming this is IMDB… what about a scalar subquery?

For example, assuming a slightly different schema from yours:

selectgenre.code as genre,
  (
select  group_concat( title )
from
(
  selectmovie.title as title
  from  movie

  join  movie_genre
  onmovie_genre.movie_id = movie.id

  where movie_genre.genre_id = genre.id

  order by  movie.title

  limit 4
) 
  ) as sample
from  genre

where genre.code = 'Western'

order by  genre.code

> Western|"26 Men" (1957),"A Man Called Shenandoah" (1965),"ABC Weekend 
> Specials" (1977) {The Winged Colt (#1.5)},"Action in the Afternoon" (1953)

CPU Time: user 0.083246 sys 0.000443

This is for:

select count( * ) from genre;
30

select count( * ) from movie;
2545331

select count( * ) from movie_genre;
1545196


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

Re: [sqlite] UPDATE question

[Marc L. Allen]

Oops.. sorry.. I missed the last paragraph.  

If you're essentially single threaded.. I can do it in two updates...

UPDATE table SET Sequence = -(Sequence + 1) WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert
UPDATE table SET Sequence = -Sequence WHERE Sequence < 0  AND Name = 
name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE question

[Marc L. Allen]

How about... ?

UPDATE table SET Sequence = Sequence + 1 WHERE Sequence >= seq_to_insert AND 
Name = name_to_insert

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Peter Haworth
Sent: Thursday, September 05, 2013 2:21 PM
To: sqlite-users
Subject: [sqlite] UPDATE question

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For 
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with 
sequence >=3, sorting them by descending sequence, then a loop with an UPDATE 
to set the sequence to sequence+1 for each PKey value so 5 becomes 6, 4 becomes 
5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement that 
could do this for me.  I can use the WHERE clause to select sequence 3,4, and 5 
but  the UPDATE has to process the rows in descending sequence order to avoid 
UNIQUE conflicts.


Pete
lcSQL Software 
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE question

[Peter Haworth]

I have a table with the following (simplified) structure

PKeyINTEGER PRIMARY KEY
NameTEXT
Sequence INTEGER

The Name/Sequence pair of columns is defined as UNIQUE

I need to insert a new row into a point between two sequence numbers.  For
example, if the existing rows are:

Name   Sequence
---
Blue   1
Blue   2
Blue3
Blue   4
Blue   5

... I might need to insert a new Blue/3.

Currently I'm doing this by selecting the Pkey values of the entries with
sequence >=3, sorting them by descending sequence, then a loop with an
UPDATE to set the sequence to sequence+1 for each PKey value so 5 becomes
6, 4 becomes 5, 3 becomes 4.  Then I INSERT a new Blue/3 row.

That works fine but wondering if there might be a single UPDATE statement
that could do this for me.  I can use the WHERE clause to select sequence
3,4, and 5 but  the UPDATE has to process the rows in descending sequence
order to avoid UNIQUE conflicts.


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

Re: [sqlite] Collating sequences. Was: Query problems

[Richard Hipp]

On Thu, Sep 5, 2013 at 9:40 AM, Marc L. Allen
wrote:

> The left-most of the first select?  Or the second?  Maybe I don't
> understand 'left-most?'
>

The left-most SELECT statement in the query where the column has an
explicit collating sequence.  In other words:  the first SELECT statement
of the query that has an explicitly defined collating sequence.

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

Re: [sqlite] Collating sequences. Was: Query problems

[Marc L. Allen]

The left-most of the first select?  Or the second?  Maybe I don't understand 
'left-most?'

-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Richard Hipp
Sent: Thursday, September 05, 2013 9:36 AM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Collating sequences. Was: Query problems

On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik  wrote:

> On 9/5/2013 7:31 AM, Richard Hipp wrote:
>
>> There seems to be some misinformation circulating in this thread.  
>> Please let me try to clear up a few points.
>>
>
> While we are at it, an interesting question was raised upthread. What 
> happens here:
>
> create table t1(x text collate c1);
> create table t2(x text collate c2);
>
> select x from t1
> union
> select x from t2
> order by x;
>
> Which collation is used by UNION to deduplicate? Which collation is 
> used by ORDER BY to sort?
>


The left-most.  http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602

--
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/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-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Collating sequences. Was: Query problems

[Richard Hipp]

On Thu, Sep 5, 2013 at 9:31 AM, Igor Tandetnik  wrote:

> On 9/5/2013 7:31 AM, Richard Hipp wrote:
>
>> There seems to be some misinformation circulating in this thread.  Please
>> let me try to clear up a few points.
>>
>
> While we are at it, an interesting question was raised upthread. What
> happens here:
>
> create table t1(x text collate c1);
> create table t2(x text collate c2);
>
> select x from t1
> union
> select x from t2
> order by x;
>
> Which collation is used by UNION to deduplicate? Which collation is used
> by ORDER BY to sort?
>


The left-most.  http://www.sqlite.org/src/artifact/8b148eb851f?ln=1582-1602

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

Re: [sqlite] Collating sequences. Was: Query problems

[Igor Tandetnik]

On 9/5/2013 7:31 AM, Richard Hipp wrote:

There seems to be some misinformation circulating in this thread.  Please
let me try to clear up a few points.


While we are at it, an interesting question was raised upthread. What 
happens here:


create table t1(x text collate c1);
create table t2(x text collate c2);

select x from t1
union
select x from t2
order by x;

Which collation is used by UNION to deduplicate? Which collation is used 
by ORDER BY to sort?

--
Igor Tandetnik

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

Re: [sqlite] about Khmer unicode with sqlite

[John McKown]

It may also be the system which is actually doing the display to the
terminal. In my case, I was displaying a UTF-8 document on Linux, using
Konsole. I had set LC_ALL to en_US.UTF-8. The file had the UTF-8 sequence
0xe2 0x80 0x93, which is U+2013, or an "en dash". But I was seeing an
"latin small letter a with a circumflex". The reason was that Konsole
normally displays characters based on the old DEC VT100 character map. To
see the proper character, I had to run the Linux command:

echo -e "\e%G"

which sets Konsole from ISO8859-1 character set to UTF-8 character set.

I don't know what system, program, etc the OP is using, but it may need to
be customedto cause the display to render the proper glyphs.


On Thu, Sep 5, 2013 at 7:08 AM, Richard Hipp  wrote:

> On Thu, Sep 5, 2013 at 6:16 AM, Sarith San  wrote:
>
> > Dear Sir or Madam
> >
> > How do integrate Khmer unicode into sqlite?
> > When I try to use Khmer unicode with sqlite, the characters does not
> > display. All I see, they display in
> > English.
> >
>
> SQLite is a C-library.  It does not "display" anything.  You must be
> confusing SQLite with an interactive shell program of some kind that lets
> you interact with an SQLite database using typed-in commands.  The
> "display" is an operation of the shell program, not of SQLite itself.  If
> Khmer unicode is not being displayed correctly, then that is a fault in the
> shell program, not of SQLite.
>
> So what shell program are you using?
>
> D. Richard Hipp
> d...@sqlite.org
> ___
> sqlite-users mailing list
> sqlite-users@sqlite.org
> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
>



-- 
As of next week, passwords will be entered in Morse code.

Maranatha! <><
John McKown
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Sqlite handler in trigger

[Igor Tandetnik]

On 9/5/2013 5:39 AM, techi eth wrote:

I have case where one process is updating data with his private handler but
trigger on update is created by other process by his private handler.


I'm not sure I understand this sentence. When you run CREATE TRIGGER 
statement, the trigger you've just created becomes part of the database 
schema, visible to all connections (unless you do CREATE TEMP TRIGGER; 
is this what you are talking about?)


On the other hand, sqlite3_create_function() registers a custom function 
just for this connection. If you create a trigger that calls this 
function, then those connections where the function is not registered 
will fail to even prepare an UPDATE statement - SQLite will issue an 
"unknown function" error.


In general, there is no mechanism built into SQLite that would allow one 
process to be automatically notified that another process made a change 
to the database. If that's what you want, you would have to implement 
that in your application - you can't somehow trick SQLite into doing it 
for you.

--
Igor Tandetnik

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

Re: [sqlite] Documentation update request

[Markus Schaber]

Hi, Simon,

von Simon Slavin
> On 4 Sep 2013, at 3:05pm, Markus Schaber 
> wrote:
> 
> > Afaics, this applies to partial indices for similar reasons.
> 
> I did not even know partial indices was implemented.  Thank you.

It's new in 3.8.0 :-)


Best regards

Markus Schaber

CODESYS(r) a trademark of 3S-Smart Software Solutions GmbH

Inspiring Automation Solutions

3S-Smart Software Solutions GmbH
Dipl.-Inf. Markus Schaber | Product Development Core Technology
Memminger Str. 151 | 87439 Kempten | Germany
Tel. +49-831-54031-979 | Fax +49-831-54031-50

E-Mail: m.scha...@codesys.com | Web: http://www.codesys.com | CODESYS store: 
http://store.codesys.com
CODESYS forum: http://forum.codesys.com

Managing Directors: Dipl.Inf. Dieter Hess, Dipl.Inf. Manfred Werner | Trade 
register: Kempten HRB 6186 | Tax ID No.: DE 167014915

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

Re: [sqlite] Performance question related to multiple processes using sqlite

[Richard Hipp]

On Wed, Sep 4, 2013 at 3:51 PM, Varadan, Yamini (SCR US) (EXT) <
yamini.varadan@siemens.com> wrote:

>
> But would any one know if there is any kind of synchronization that is
> done between different processes that connect to different sqlite databases
> that might slow down one process when the other process is performing DB
> operation on its sqlite DB?
>

No.  Two processes connected to different databases operating completely
independently of one another as far as SQLite is concerned.

Even if two processes are talking to the same SQLite database, if one
process is not actively using SQLite and does not have a transaction open,
then there is no interaction with the other process.

And even if there is "interaction", that interaction is limited to file
locks and/or use of a small amount of shared memory in WAL mode.  It is
never the case that one process will block or signal another process.
-- 
D. Richard Hipp
d...@sqlite.org
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] about Khmer unicode with sqlite

[Richard Hipp]

On Thu, Sep 5, 2013 at 6:16 AM, Sarith San  wrote:

> Dear Sir or Madam
>
> How do integrate Khmer unicode into sqlite?
> When I try to use Khmer unicode with sqlite, the characters does not
> display. All I see, they display in
> English.
>

SQLite is a C-library.  It does not "display" anything.  You must be
confusing SQLite with an interactive shell program of some kind that lets
you interact with an SQLite database using typed-in commands.  The
"display" is an operation of the shell program, not of SQLite itself.  If
Khmer unicode is not being displayed correctly, then that is a fault in the
shell program, not of SQLite.

So what shell program are you using?

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] commit fails when journal_mode = memory

[Frank De prins]

Hello,

The next problem occurs since switching to sqlite 3.8; it was never observed in 
3.7.17 or earlier.
I have an application in which I insert a lot of data in a transaction, started 
by executing "begin transaction", after having executed "PRAGMA synchronous = 
0" and "PRAGMA journal_mode = MEMORY".
After that the inserts take place.
When I finally try to execute the "commit" statement, I get an SQLITE_BUSY 
status.
This error does not happen when I remove the journal_mode statement.
The latest version tested is 3.8.0.2.
I build the library with the next defines:
THREADSAFE=2
SQLITE_ENABLE_FTS4
SQLITE_ENABLE_FTS3_PARENTHESIS
SQLITE_ENABLE_RTREE
SQLITE_ENABLE_COLUMN_METADATA
SQLITE_ENABLE_STAT3
SQLITE_CORE
The application is built with the same defines, except SQLITE_CORE which is 
then ommitted.

Frank De prins
 [cid:image002.jpg@01CE45B7.374DA450]   Advisory & Software for
Real Estate & Facility Management
Sneeuwbeslaan 20 b3 ∙ 2610 Antwerp ∙ Belgium
t. +32 (0) 3 829 04 95
frank.depr...@mcs.fm ∙  
www.mcs.fm

http://www.mcs.fm/legal/email-disclaimer/




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

[sqlite] Performance question related to multiple processes using sqlite

[Varadan, Yamini (SCR US) (EXT)]

Hello,
We use Qt with sqldriver Sqlite-4 in our application. (Windows XP 32 bit, 
Visual studio 2005)
We are facing a performance issue in the following scenario.

There are two processes A and B. A uses sqlite DB1 and keeps populating data.
Process B uses sqlite DB2 for writing and occasionally opens DB1 for reading.

What we notice is when A is writing into DB1, B hangs for a couple of seconds 
even though it is not performing any DB operations on DB1/DB2.

On opening Windows event viewer, we see that process B is making a lot of 
registry calls and is accessing disk a lot during the time that process A is 
writing to DB1 and process B really is not doing any DB operation at that time.

For now, we have not been able to identify if this is happenning inside Qt sql 
module or inside sqlite.

But would any one know if there is any kind of synchronization that is done 
between different processes that connect to different sqlite databases that 
might slow down one process when the other process is performing DB operation 
on its sqlite DB?

Thanks in advance!

This message and any attachments are solely for the use of intended recipients. 
The information contained herein may include trade secrets, protected health or 
personal information, privileged or otherwise confidential information. 
Unauthorized review, forwarding, printing, copying, distributing, or using such 
information is strictly prohibited and may be unlawful. If you are not an 
intended recipient, you are hereby notified that you received this email in 
error, and that any review, dissemination, distribution or copying of this 
email and any attachment is strictly prohibited. If you have received this 
email in error, please contact the sender and delete the message and any 
attachment from your system. Thank you for your cooperation
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] about Khmer unicode with sqlite

[Sarith San]

Dear Sir or Madam

How do integrate Khmer unicode into sqlite?
When I try to use Khmer unicode with sqlite, the characters does not display. 
All I see, they display in
English.

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

[sqlite] Collating sequences. Was: Query problems

[Richard Hipp]

There seems to be some misinformation circulating in this thread.  Please
let me try to clear up a few points.

If you give a collating sequence to a column in a table definition, then
that collating sequence becomes the default for that column:

CREATE TABLE t1(pqr TEXT COLLATE xyzzy);

In the table above, the pqr column has a default collating sequence of
xyzzy.  Any time you do a comparison against that column the default
collating sequence is used.  So, for example, if you say:

   SELECT * FROM t1 WHERE pqr=?1;

Then the xyzzy collating sequence is used to compare the input string bound
to ?1 against the value of the column pqr.  It is *not* necessary to
explicitly add a COLLATE clause to the comparison:

   SELECT * FROM t1 WHERE pq4=?1 COLLATE xyzzy;

Though not necessary, adding the COLLATE clause is harmless in this case.

If you create an index on a column, that index automatically uses the
default collating sequence, unless you specify an alternative.  So, for
example, if you say:

   CREATE INDEX t1pqr ON t1(pqr);

Then the xyzzy collating sequence is used for the index as well.  It is
*not* necessary to explicitly add a COLLATE clause to the index:

   CREATE INDEX t1pqr ON t1(pqr COLLATE xyzzy);

On the other hand, doing so is a harmless no-op.

Creating an index with a collating sequence that differs from the default
does not change the default collating sequence of the column.  Hence, if
you say:

   CREATE TABLE t2(mno TEXT);
   CREATE INDEX t2mno1 ON t2(mno COLLATE xyzzy);
   SELECT * FROM t2 WHERE mno=?2;

The comparison in the query uses the default collating sequence for the
column mno and hence cannot make use of the index.  But if you query this
way:

   SELECT * FROM t2 WHERE mno=?3 COLLATE xyzzy;

Then the collating sequence of the index matches the collating sequence of
the query and the index can be used.



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

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

[Brzozowski, Christoph]

Thank you very much for the responses. That was helpful.



With best regards,
Christoph Brzozowski

Siemens AG
Industry Sector
Industry Automation Division
Industrial Automation Systems
Process Automation
I IA AS PA CIS R 5
Karl-Legien-Str. 190
53117 Bonn, Germany
Tel: +49 228 64805-215
mailto:christoph.brzozow...@siemens.com

Siemens Aktiengesellschaft: Chairman of the Supervisory Board: Gerhard Cromme; 
Managing Board: Joe Kaeser, Chairman, President and Chief Executive Officer; 
Roland Busch, Brigitte Ederer, Klaus Helmrich, Barbara Kux, Hermann Requardt, 
Siegfried Russwurm, Peter Y. Solmssen, Michael Suess; Registered offices: 
Berlin and Munich, Germany; Commercial registries: Berlin Charlottenburg, HRB 
12300, Munich, HRB 6684; WEEE-Reg.-No. DE 23691322
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-boun...@sqlite.org] 
On Behalf Of Joe Mistachkin
Sent: Donnerstag, 5. September 2013 08:08
To: 'General Discussion of SQLite Database'
Subject: Re: [sqlite] Different read/write behaviour when using 
System.Data.SQLite .NET Wrapper and using database encryption through a 
password leads to data inconsistency


Brzozowski, Christoph wrote:
>
> Our application uses the System.Data.SQLite .NET Wrapper ( version
1.0.66.0 )
>

That version was released in April 2010 and is no longer officially supported.

> 
> in a multi user scenario, where multiple users on different machines
access the
> same database, which lies on a network share, or in a single user
scenario, where
> the database lies locally but is accessed simultaneously by multiple
processes
> on the same machine. 
> 

Sharing a SQLite database file using a network share on Windows can be 
problematic, please refer to the following link for further information:

https://www.sqlite.org/faq.html#q5

> 
> When we switched the database to an encrypted one, by adding a 
> password to
the
> connection string passed to the ADO.NET Sqlite provider, the
synchronization
> mechanisms ceased to work. 
> 

The CryptoAPI-based encryption included with System.Data.SQLite is a legacy 
feature, has known issues, and is officially unsupported.  It is being retained 
only for the purpose of backward compatibility with legacy applications that 
make use of it.

That all being said, you might wish to try using the latest released version of 
System.Data.SQLite.

Alternatively, you might want to look into the commercial SEE extension, which 
does work with System.Data.SQLite and is fully supported for use with it.

--
Joe Mistachkin

___
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] Sqlite handler in trigger

[techi eth]

Hi,

 I would like to check sqlite handler usability while using trigger:

I have register one function to sqlite by sqlite3_create_function().Now I
am creating trigger on update & selecting that function for callaback.

In above scenario it is mandatory that sqlite3 handler used while create
function & setting trigger should use for update.

I have case where one process is updating data with his private handler but
trigger on update is created by other process by his private handler.

Cheers -

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

Re: [sqlite] Different read/write behaviour when using System.Data.SQLite .NET Wrapper and using database encryption through a password leads to data inconsistency

[Joe Mistachkin]

Brzozowski, Christoph wrote:
>
> Our application uses the System.Data.SQLite .NET Wrapper ( version
1.0.66.0 )
>

That version was released in April 2010 and is no longer officially
supported.

> 
> in a multi user scenario, where multiple users on different machines
access the
> same database, which lies on a network share, or in a single user
scenario, where
> the database lies locally but is accessed simultaneously by multiple
processes
> on the same machine. 
> 

Sharing a SQLite database file using a network share on Windows can be
problematic,
please refer to the following link for further information:

https://www.sqlite.org/faq.html#q5

> 
> When we switched the database to an encrypted one, by adding a password to
the
> connection string passed to the ADO.NET Sqlite provider, the
synchronization
> mechanisms ceased to work. 
> 

The CryptoAPI-based encryption included with System.Data.SQLite is a legacy
feature,
has known issues, and is officially unsupported.  It is being retained only
for the
purpose of backward compatibility with legacy applications that make use of
it.

That all being said, you might wish to try using the latest released version
of
System.Data.SQLite.

Alternatively, you might want to look into the commercial SEE extension,
which does
work with System.Data.SQLite and is fully supported for use with it.

--
Joe Mistachkin

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