[sqlite] ANN: SQLite PHP Generator 15.12 released

[SQL Maestro Group]

Hi!

SQL Maestro Group announces the release of SQLite PHP Generator 15.12, a 
powerful GUI frontend for Windows that allows you to generate feature-rich 
CRUD web applications for your SQLite database.
http://www.sqlmaestro.com/products/sqlite/phpgenerator/

Online demo:
http://demo.sqlmaestro.com/

Top 15 new features:


1. New modern look and feel.
2. 100% responsive design.
3. PHP 7 support.
4. Top side drop-down menus.
5. Enhanced Filter Builder.
6. Multi-column sorting.
7. Keyboard shortcuts.
8. A number of new and updated controls.
9. 18 color themes.
10. Font-based icons.
11. Event management enhancements.
12. Less preprocessor syntax for user-defined styles.
13. HTML filter.
14. New and updated data access drivers.
15. PHP Generator UI improvements.

Full press-release is available at:
http://www.sqlmaestro.com/news/company/php_generator_15_12_released/

Background information:
---
SQL Maestro Group offers complete database admin, development and management 
tools for MySQL, SQL Server, PostgreSQL, Oracle, DB2, SQLite, SQL Anywhere, 
Firebird and MaxDB providing the highest performance, scalability and 
reliability to meet the requirements of today's database applications.

Sincerely yours,
The SQL Maestro Group Team
http://www.sqlmaestro.com 

[sqlite] Problem with accumulating decimal values

[Keith Medcalf]

> I was taught "Round [only] before printing.".  These days it would be
> something like "Round [only] before your API returns to the calling
> program.

Those are not the same.  Round only before printing (whether to the screen or 
to a printer).  In other words, rounding is a way to make things palatable for 
humans.  Therefore, the rounding function should only be used when the next 
step is presenting the value to a human.  

If the value is not being directly presented to a human, then you should not be 
rounding (yet).

In other words, you do not apply the rounding when your API returns (for 
example, in the sin() function).  You apply rounding only when the next 
consumer is a human.  If there is the slightest possibility that the next 
consumer is not a human, you should not be rounding.

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

>  says:
>> To cast a BLOB value to TEXT, the sequence of bytes that make up the
>> BLOB is interpreted as text encoded using the database encoding.
>
> (The database encoding must be set when the DB file is created.)

Thank you! That's the information that I was looking for.

Regards,
Michael

[sqlite] compile configure

[王庆刚]

hi,all
When I compile the sqlite3 source, will I select difference parameter 
effect the performance of the sqlite funtion such as speed?

[sqlite] batch or one by one?

[王庆刚]

Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and 
sqlite3_step together.  In fact.



At 2015-12-17 14:48:18, "Igor Tandetnik"  wrote:
>On 12/16/2015 11:24 PM, ??? wrote:
>>   There is an interesting phenomenon.As you know, SQLite can retrieve 
>> records by batch or one by one.
>>   1.Retrieve by batch such as sqlite3_get_table.
>>   2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step 
>> together.
>
>If you look at the implementation of sqlite3_get_table, it works by 
>calling _prepare and _step and so on. It's not some kind of alternative 
>interface to SQLite, merely a wrapper.
>-- 
>Igor Tandetnik
>
>___
>sqlite-users mailing list
>sqlite-users at mailinglists.sqlite.org
>http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

[sanhua.zh]

I try to use shared cache to optimize my code. Sincesqlite3_enable_shared_cache 
is deprecated on iOS/Mac, I usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag 
to open shared cache mode.
4 threads select is running in my code, while each thread has its own sqlite 
connection and do the same thing - select all 10 item from ?test? table.
Strange thing happened.
Each thread ends up within 0.09 seconds without SQLITE_OPEN_SHAREDCACHE, but 
with SQLITE_OPEN_SHAREDCACHE ends up in 14 second, which is much slower.
As sqlite.org said, shared cache mode can reduce the memory and IO, which leads 
to better performance.
Any one can tell me whether I write the wrong code or using shared cache mode 
in an incorrect scene ?


Here is my code mixed by C and Objective-C, but it will not stop your reading 
and understanding:


#import Foundation/Foundation.h
#import sqlite3.h
#import sys/time.h


double now()
{
  timeval now;
  gettimeofday(now, nullptr);
  return now.tv_sec+now.tv_usec/100.0;
}


void showResultCode(int resultCode)
{
  if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
NSLog(@"unexperted result %d", resultCode);
  }
}


void SQLiteLog(void* userInfo, int ret, const char* msg)
{
  NSLog(@"ret=%d, msg=%s", ret, msg);
}


void write(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open(path, handle));


  showResultCode(sqlite3_exec(handle, "PRAGMA synchronous=FULL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "drop table if exists test;", nullptr, 
nullptr, nullptr));
  showResultCode(sqlite3_exec(handle, "create table if not exists test(id 
integer);", nullptr, nullptr, nullptr));


  sqlite3_stmt* stmt = nullptr;
  showResultCode(sqlite3_exec(handle, "BEGIN", nullptr, nullptr, nullptr));
  for (int i = 0; i  100; i++) {
showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
into test values(%d);", i].UTF8String, -1, stmt, nullptr));
showResultCode(sqlite3_step(stmt));
showResultCode(sqlite3_finalize(stmt));
  }
  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
  showResultCode(sqlite3_close(handle));
}




void read(const char* path)
{
  sqlite3* handle;


  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE | 
SQLITE_OPEN_SHAREDCACHE, nullptr));
//  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE, 
nullptr));
  sqlite3_stmt* stmt;
  showResultCode(sqlite3_prepare(handle, "select * from test;", -1, stmt, 
nullptr));


  double start = now();
  int integer = 0;
  while (sqlite3_step(stmt)!=SQLITE_DONE) {
integer = sqlite3_column_int(stmt, 0);
  }
  NSLog(@"%d", integer);
  showResultCode(sqlite3_finalize(stmt));
  double end = now();
  NSLog(@"cost %f", end-start);


  showResultCode(sqlite3_close(handle));
}


int main(int argc, char * argv[])
{
  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);
  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);


  const char* path = "/Users/sanhuazhang/Desktop/test.db";


  write(path);
  for (int i = 0; i  4; i++) {
dispatch_queue_t queue = dispatch_queue_create([NSString 
stringWithFormat:@"queue%d", i].UTF8String, DISPATCH_QUEUE_CONCURRENT);
dispatch_async(queue, ^{
  read(path);
});
  }


  sleep(1);
  return 1;
}

[sqlite] Missing documentation about BLOB encoding conversions

[Clemens Ladisch]

Michael Kaufmann wrote:
>>> It seems that BLOBs are converted from UTF-8 to UTF-16 when 
>>> sqlite3_column_text16() is called.
>
> The sequence of calls is:
>
> 1. sqlite3_step()
> 2. sqlite3_column_text16()

 says:
> To cast a BLOB value to TEXT, the sequence of bytes that make up the
> BLOB is interpreted as text encoded using the database encoding.

(The database encoding must be set when the DB file is created.)


Regards,
Clemens

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

The sequence of calls is:

1. sqlite3_step()
2. sqlite3_column_text16()
3. sqlite3_column_bytes()

Please see the attached example program.

Regards,
Michael


> What is the exact sequence of calls?
>
> If you call sqlite3_column_text() on a blob value, the new type will  
> be text and a subsequent call to sqlite_column_text16() must by  
> definition perform transcoding.
>
> -Urspr?ngliche Nachricht-
> Von: sqlite-users-bounces at mailinglists.sqlite.org  
> [mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von  
> Michael Kaufmann
> Gesendet: Donnerstag, 17. Dezember 2015 10:41
> An: sqlite-users at mailinglists.sqlite.org
> Betreff: [sqlite] Missing documentation about BLOB encoding conversions
>
> Hi,
>
> I expected that BLOB data is returned unchanged by  
> sqlite3_column_blob(), sqlite3_column_text() and  
> sqlite3_column_text16(). The documentation at  
> https://www.sqlite.org/c3ref/column_blob.html says: "Type  
> conversions and pointer invalidations might occur in the following  
> cases: ... The initial content is a BLOB and sqlite3_column_text() or
> sqlite3_column_text16() is called. A zero-terminator might need to  
> be added to the string."
>
> I have found out that SQLite does more than just adding a zero  
> terminator. It seems that BLOBs are converted from UTF-8 to UTF-16  
> when sqlite3_column_text16() is called.
>
> This is quite unexpected, and it would be nice if the rules for BLOB  
> encodings and BLOB encoding conversions were pointed out in the  
> documentation.
>
> Regards,
> Michael
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___
>  Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: hick at scigames.at
>
> This communication (including any attachments) is intended for the  
> use of the intended recipient(s) only and may contain information  
> that is confidential, privileged or legally protected. Any  
> unauthorized use or dissemination of this communication is strictly  
> prohibited. If you have received this communication in error, please  
> immediately notify the sender by return e-mail message and delete  
> all copies of the original communication. Thank you for your  
> cooperation.
>
>
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Problem with accumulating decimal values

[Simon Slavin]

On 17 Dec 2015, at 4:22am, R Smith  wrote:

> it was a seemingly too-convoluted detour for simply saying: "Don't store 
> rounded numbers. Round only the results."

I was taught "Round [only] before printing.".  These days it would be something 
like "Round [only] before your API returns to the calling program.

Another way to think of it is to ask yourself "At this point, should I be 
processing reals or integers ?" and to round only when you need to be handling 
integers.

Simon.

[sqlite] batch or one by one?

[Simon Slavin]

On 17 Dec 2015, at 4:24am, ??? <2004wqg2008 at 163.com> wrote:

> 1.Retrieve by batch such as sqlite3_get_table.

As the documentation says, please do not use sqlite3_get_table().  It was 
written a long time ago and better calls are now available.

"This is a legacy interface that is preserved for backwards compatibility. Use 
of this interface is not recommended."

You may use sqlite3_exec() instead if you want.

> 2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step 
> together.
> Using the above two methods to retrieve hundreds of records by random, 
> which method is fast?

As the documentation says

sqlite3_get_table() is a wrapper around sqlite3_exec().  In other words it 
calls sqlite3_exec() and does some other things.  So it cannot be faster than 
sqlite3_exec().

sqlite3_exec() is a wrapper around sqlite3_prepare_v2(), sqlite3_step(), and 
sqlite3_finalize().  In other words it calls those routines and does some other 
things.  So it cannot be faster than they are.

Simon.

[sqlite] batch or one by one?

[王庆刚]

hi,all

 There is an interesting phenomenon.As you know, SQLite can retrieve 
records by batch or one by one.
 1.Retrieve by batch such as sqlite3_get_table.
 2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step together.
 Using the above two methods to retrieve hundreds of records by random, 
which method is fast?

 best regard!
 WQG

[sqlite] SQLite take lower performance while using shared cache on iOS/Mac

[Scott Perry]

Using a shared cache will result in lower memory usage and may result in lower 
IO, but it isn't likely to speed up your program since it also requires more 
locks to guarantee safety between competing database connections.

SQLite on Apple's platforms is built with SQLITE_THREADSAFE=2, so under normal 
use there is minimal locking overhead.

Unless you absolutely need to conserve every last byte of memory?and since 
you're writing Objective-C that's almost certainly not the case?you should not 
be using a shared cache.

On Dec 16, 2015, at 10:58 PM, sanhua.zh  wrote:
> 
> I try to use shared cache to optimize my code. 
> Sincesqlite3_enable_shared_cache is deprecated on iOS/Mac, I 
> usesqlite3_open_v2 withSQLITE_OPEN_SHAREDCACHE flag to open shared cache mode.
> 4 threads select is running in my code, while each thread has its own sqlite 
> connection and do the same thing - select all 10 item from ?test? table.
> Strange thing happened.
> Each thread ends up within 0.09 seconds without SQLITE_OPEN_SHAREDCACHE, but 
> with SQLITE_OPEN_SHAREDCACHE ends up in 14 second, which is much slower.
> As sqlite.org said, shared cache mode can reduce the memory and IO, which 
> leads to better performance.
> Any one can tell me whether I write the wrong code or using shared cache mode 
> in an incorrect scene ?
> 
> 
> Here is my code mixed by C and Objective-C, but it will not stop your reading 
> and understanding:
> 
> 
> #import Foundation/Foundation.h
> #import sqlite3.h
> #import sys/time.h
> 
> 
> double now()
> {
>  timeval now;
>  gettimeofday(now, nullptr);
>  return now.tv_sec+now.tv_usec/100.0;
> }
> 
> 
> void showResultCode(int resultCode)
> {
>  if (resultCode!=SQLITE_DONEresultCode!=SQLITE_OKresultCode!=SQLITE_ROW) {
>NSLog(@"unexperted result %d", resultCode);
>  }
> }
> 
> 
> void SQLiteLog(void* userInfo, int ret, const char* msg)
> {
>  NSLog(@"ret=%d, msg=%s", ret, msg);
> }
> 
> 
> void write(const char* path)
> {
>  sqlite3* handle;
> 
> 
>  showResultCode(sqlite3_open(path, handle));
> 
> 
>  showResultCode(sqlite3_exec(handle, "PRAGMA synchronous=FULL", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "PRAGMA journal_mode=WAL", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "drop table if exists test;", nullptr, 
> nullptr, nullptr));
>  showResultCode(sqlite3_exec(handle, "create table if not exists test(id 
> integer);", nullptr, nullptr, nullptr));
> 
> 
>  sqlite3_stmt* stmt = nullptr;
>  showResultCode(sqlite3_exec(handle, "BEGIN", nullptr, nullptr, nullptr));
>  for (int i = 0; i  100; i++) {
>showResultCode(sqlite3_prepare(handle, [NSString stringWithFormat:@"insert 
> into test values(%d);", i].UTF8String, -1, stmt, nullptr));
>showResultCode(sqlite3_step(stmt));
>showResultCode(sqlite3_finalize(stmt));
>  }
>  showResultCode(sqlite3_exec(handle, "COMMIT", nullptr, nullptr, nullptr));
>  showResultCode(sqlite3_close(handle));
> }
> 
> 
> 
> 
> void read(const char* path)
> {
>  sqlite3* handle;
> 
> 
>  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE | 
> SQLITE_OPEN_SHAREDCACHE, nullptr));
> //  showResultCode(sqlite3_open_v2(path, handle, SQLITE_OPEN_READWRITE, 
> nullptr));
>  sqlite3_stmt* stmt;
>  showResultCode(sqlite3_prepare(handle, "select * from test;", -1, stmt, 
> nullptr));
> 
> 
>  double start = now();
>  int integer = 0;
>  while (sqlite3_step(stmt)!=SQLITE_DONE) {
>integer = sqlite3_column_int(stmt, 0);
>  }
>  NSLog(@"%d", integer);
>  showResultCode(sqlite3_finalize(stmt));
>  double end = now();
>  NSLog(@"cost %f", end-start);
> 
> 
>  showResultCode(sqlite3_close(handle));
> }
> 
> 
> int main(int argc, char * argv[])
> {
>  sqlite3_config(SQLITE_CONFIG_LOG, SQLiteLog, NULL);
>  sqlite3_config(SQLITE_CONFIG_MULTITHREAD);
> 
> 
>  const char* path = "/Users/sanhuazhang/Desktop/test.db";
> 
> 
>  write(path);
>  for (int i = 0; i  4; i++) {
>dispatch_queue_t queue = dispatch_queue_create([NSString 
> stringWithFormat:@"queue%d", i].UTF8String, DISPATCH_QUEUE_CONCURRENT);
>dispatch_async(queue, ^{
>  read(path);
>});
>  }
> 
> 
>  sleep(1);
>  return 1;
> }
> ___
> sqlite-users mailing list
> sqlite-users at mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Missing documentation about BLOB encoding conversions

[Michael Kaufmann]

Hi,

I expected that BLOB data is returned unchanged by  
sqlite3_column_blob(), sqlite3_column_text() and  
sqlite3_column_text16(). The documentation at  
https://www.sqlite.org/c3ref/column_blob.html says: "Type conversions  
and pointer invalidations might occur in the following cases: ... The  
initial content is a BLOB and sqlite3_column_text() or  
sqlite3_column_text16() is called. A zero-terminator might need to be  
added to the string."

I have found out that SQLite does more than just adding a zero  
terminator. It seems that BLOBs are converted from UTF-8 to UTF-16  
when sqlite3_column_text16() is called.

This is quite unexpected, and it would be nice if the rules for BLOB  
encodings and BLOB encoding conversions were pointed out in the  
documentation.

Regards,
Michael

[sqlite] Missing documentation about BLOB encoding conversions

[Hick Gunter]

What is the exact sequence of calls?

If you call sqlite3_column_text() on a blob value, the new type will be text 
and a subsequent call to sqlite_column_text16() must by definition perform 
transcoding.

-Urspr?ngliche Nachricht-
Von: sqlite-users-bounces at mailinglists.sqlite.org 
[mailto:sqlite-users-bounces at mailinglists.sqlite.org] Im Auftrag von Michael 
Kaufmann
Gesendet: Donnerstag, 17. Dezember 2015 10:41
An: sqlite-users at mailinglists.sqlite.org
Betreff: [sqlite] Missing documentation about BLOB encoding conversions

Hi,

I expected that BLOB data is returned unchanged by sqlite3_column_blob(), 
sqlite3_column_text() and sqlite3_column_text16(). The documentation at 
https://www.sqlite.org/c3ref/column_blob.html says: "Type conversions and 
pointer invalidations might occur in the following cases: ... The initial 
content is a BLOB and sqlite3_column_text() or
sqlite3_column_text16() is called. A zero-terminator might need to be added to 
the string."

I have found out that SQLite does more than just adding a zero terminator. It 
seems that BLOBs are converted from UTF-8 to UTF-16 when 
sqlite3_column_text16() is called.

This is quite unexpected, and it would be nice if the rules for BLOB encodings 
and BLOB encoding conversions were pointed out in the documentation.

Regards,
Michael

___
sqlite-users mailing list
sqlite-users at mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: hick at scigames.at

This communication (including any attachments) is intended for the use of the 
intended recipient(s) only and may contain information that is confidential, 
privileged or legally protected. Any unauthorized use or dissemination of this 
communication is strictly prohibited. If you have received this communication 
in error, please immediately notify the sender by return e-mail message and 
delete all copies of the original communication. Thank you for your cooperation.

[sqlite] batch or one by one?

[Stephan Beal]

On Thu, Dec 17, 2015 at 8:04 AM, ??? <2004wqg2008 at 163.com> wrote:

> Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and
> sqlite3_step together.  In fact.
>
>
And uses, on average, much more memory, as it stores all rows for the query
results in the result table. If your results have 10 rows, that method will
use, abstractly speaking, 10x as much memory. prepare/step allows code to
have a more or less constant memory usage, independent of the number of
rows in the result set.

The very first line of the get_table documentation says:

This is a legacy interface that is preserved for backwards compatibility.
Use of this interface is not recommended.


-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
http://gplus.to/sgbeal
"Freedom is sloppy. But since tyranny's the only guaranteed byproduct of
those who insist on a perfect world, freedom will have to do." -- Bigby Wolf

[sqlite] Problem with accumulating decimal values

[R Smith]

On 2015/12/17 3:26 AM, James K. Lowden wrote:
>> Calculated errors are fine because we can at any time revisit the
>> calculation procedures, we can refine and perhaps opt for more
>> significant digits - but we can ALWAYS guarantee the accuracy-level
>> of the calculated result. However, storing wrong values (or let's
>> call them "approximate" values if you like) is pure evil.
> I'm not sure what you mean.

Yes, it was quite misunderstood - my apologies though, it was a 
seemingly too-convoluted detour for simply saying: "Don't store rounded 
numbers. Round only the results."

(Which I'm hoping we do agree on).

[sqlite] batch or one by one?

[Igor Tandetnik]

On 12/17/2015 2:04 AM, ??? wrote:
> Testing shows that sqlite3_get_table is faster than sqlite3_prepare_v2 and 
> sqlite3_step together.  In fact.

You must be doing something wrong in your test harness. You are likely 
measuring something other than actual SQLite performance. Show your code.
-- 
Igor Tandetnik

[sqlite] batch or one by one?

[Igor Tandetnik]

On 12/16/2015 11:24 PM, ??? wrote:
>   There is an interesting phenomenon.As you know, SQLite can retrieve 
> records by batch or one by one.
>   1.Retrieve by batch such as sqlite3_get_table.
>   2.Retrieve one by one such as sqlite3_prepare_v2 and sqlite3_step 
> together.

If you look at the implementation of sqlite3_get_table, it works by 
calling _prepare and _step and so on. It's not some kind of alternative 
interface to SQLite, merely a wrapper.
-- 
Igor Tandetnik