Re: [sqlite] SQLite Native in PHP in the future?

[Tim Streater]

On 13 Jan 2013 at 10:17, Tracy Rohan  wrote: 

> Hi, I read that SQLite may not be native in PHP in the future?  When I say
> native, I mean that platform/devices will need to install SQLite as an
> extension before it will function (as it was previously).  Is this true?

Why not ask on the PHP Users' List? (f'rinstance).



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

Re: [sqlite] Concurrent read performance

[Howard Chu]

Wayne Bradney wrote:

All access in SQLite is serialized. Apologies if I'm missing something 
fundamental here, but that's not what I'm seeing with a file-backed database 
when shared cache is OFF.My test has a single table with 1M rows, and four 
queries that each yield 100K different rows. I run them two ways: 1. All 
queries in a loop on the same thread in the same connection.2. Each query in 
parallel on separate threads, each with its own connection. If all access were 
serialized, I would expect these two tests to take about the same amount of 
time overall, wouldn't I?In fact, with a file-backed database and shared cache 
OFF, the second run takes about 70% less time.With shared cache ON, they're the 
same. As to your second point, I probably should have made it clear that this 
isn't an internal project, it's a software product, and we don't control where 
it runs. I understand what an SSD is and why it's better than a spindle drive, 
but my question wasn't really meant to solicit suggestions for performa

n
c

  e improvements outside the proposal at hand, which was to retire our existing 
home-grown in-memory cache implementation (which is very fast for concurrent 
reads, but is extremely limited in how it can be queried), and replace it with a 
SQL-capable, relational store and still get roughly the same performance. Our 
expectation was that we could achieve this with SQLite, but were surprised by the 
apparent lack of read-concurrency, and wanted to get some input on what our 
options might be in terms of SQLite configuration of memory-backed databases. > 
From: slav...@bigfraud.org


You should look into MDB, which does no locking for read operations. Reads 
scale perfectly across arbitrarily many CPUs. More info here


http://symas.com/mdb/

and SQLite ported to use MDB as its backend is available here

https://gitorious.org/mdb/sqlightning


Date: Sat, 12 Jan 2013 17:48:56 +
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] Concurrent read performance


On 12 Jan 2013, at 5:38pm, Wayne Bradney  wrote:


"mode=memory=shared"




1. when shared cache is enabled, all reads are serialized, and


All access in SQLite is serialised.  All transactions require locking the 
entire database.  SQLite is very simple -- 'lite' -- so queries run extremely 
quickly, so you don't normally realise that any locking has taken place.


2. there doesn't seem to be any way to have a memory-backed database that can 
be accessed by multiple connections without using a shared cache,  then I guess 
I MUST use a file-backed database to get concurrent reads, even though I don't 
need the persistence and don't want to take the I/O hit. Am I making any sense? 
Anything I'm missing?


You are putting programming effort into making your code fast, and this is 
costing you (or your employer) programmer time.


For any reasonably useful piece of software, every moment of programmer time 
invested in proper coding saves eons of user time. Putting programmer effort 
into making correct code fast is always The Right Thing to Do. Software that 
delivers the correct answer, late, is still wrong.


--
  -- Howard Chu
  CTO, Symas Corp.   http://www.symas.com
  Director, Highland Sun http://highlandsun.com/hyc/
  Chief Architect, OpenLDAP  http://www.openldap.org/project/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] SQLite cross-platform error

[Kulcsár István]

Dear sqlite-users!
I'm going to make a Visual C++ application, which is able to read database that 
was created on server side. However I'm not able to read database that was 
generated on the server from Windows.
I've successfully compiled the lates "sqlite-3071502" into my Visual Studio 
2008 project. I use the same library in Ubuntu and I could successfully compile 
into my CGI project with gcc.
I'm able to create the database file with CGI, called from PHP, however when 
I'm going to read it back on Windows, the database is malformed.
(I use CGI because PHP encountered exactly the same error, and I wanted to try 
some other way)

Here is a snippet from my CGI code:
sqlite3 *hDb;
int nRet  = sqlite3_open(File, );
if(nRet != SQLITE_OK)
{
printf("ERROR - %s\n", sqlite3_errmsg(hDb));
}

char *szErrMsg = 0;
int rc = sqlite3_exec(hDb, "CREATE TABLE groups(id INTEGER PRIMARY KEY 
AUTOINCREMENT, abbr TEXT, name TEXT)", NULL, 0, );
if(rc != SQLITE_OK)
{
printf("%s\n", szErrMsg);
sqlite3_free(szErrMsg);
}

const char *pSQL[21];
pSQL[0] = "INSERT INTO groups(abbr, name) VALUES ('001', 'Alan')";
pSQL[1] = "INSERT INTO groups(abbr, name) VALUES ('002', 'Alan')";
.
.
.
pSQL[20] = "INSERT INTO groups(abbr, name) VALUES ('021', 'Alan')";

for(int i = 0; i < 21; i++)
{
printf("%s\n", pSQL[i]);
int rc = sqlite3_exec(hDb, pSQL[i], NULL, 0, );
if(rc != SQLITE_OK)
{
printf("%s\n", szErrMsg);
sqlite3_free(szErrMsg);
continue;
}
}

sqlite3_close(hDb);

The error is actually very interresting. When I open the CGI created database 
from SqLite3 shell with Command Prompt the following happens:
sqlite> PRAGMA encoding;
UTF-8

sqlite> select * from groups;
Error: database disk image is malformed

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE groups(id INTEGER PRIMARY KEY AUTOINCREMENT, abbr TEXT, name TEXT);
/ ERROR: (11) database disj image is malformed */
INSERT INTO "groups" VALUES(21,'021','Alan');
.
.
.
INSERT INTO "groups" VALUES(11,'011','Alan');
/ ERROR: (11) database disj image is malformed */
/ ERROR: (11) database disj image is malformed */
/ ERROR: (11) database disj image is malformed */
ROLLBACK; -- due to errors

sqlite> PRAGMA integrity_check;
*** in database main ***
Page 3: btreeInitPage() returns error code 11
On tree page 2 cell 1: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 2: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 3: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 4: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 5: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 6: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 7: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 8: Rowid 11 out of order (prevous was 11)
On tree page 2 cell 10: Rowid 11 out of order (prevous was 13)
Corruption detected in cell 0 on page 2
Corruption detected in cell 1 on page 2
Corruption detected in cell 2 on page 2
Corruption detected in cell 3 on page 2
Corruption detected in cell 4 on page 2
Corruption detected in cell 5 on page 2
Corruption detected in cell 6 on page 2
Corruption detected in cell 7 on page 2
Fragmentation of 5 bytes reported as 0 on page 2

sqlite> select ROWID from groups;
11
11
11
11
11
11
11
11
11
13
11
12
13
14
15
16
17
18
19
20
21

I've attempted to set manually the ROWIDs, but SqLite3 simply ignored my 
settings, and results the exact same error.

What is more interresting:
sqlite> SELECT * FROM groups WHERE id > 11;
12|012|Alan
13|013|Alan
14|014|Alan
15|015|Alan
16|016|Alan
17|017|Alan
18|012|Alan
19|019|Alan
20|020|Alan
21|021|Alan

When I read  the CGI generated sqlite3 database file with the following code; 
the result is just what it should be.
print_r(SQLite3::version());
echo "";

$pDb = new SQLite3('default.cfg');

$result = $pDb->query('SELECT * FROM groups');
if(!$result) {
echo "ERROR: " . $pDb->lastErrorMsg();
}

while($row = $result->fetchArray(SQLITE3_ASSOC)) {
if(!isset($row['abbr']) || !isset($row['name'])) continue;
print_r($row);
echo "";
}

$pDb->close();

Array ( [versionString] => 3.6.22 [versionNumber] => 3006022 )
Array ( [id] => 1 [abbr] => 001 [name] => Alan )
Array ( [id] => 2 [abbr] => 002 [name] => Alan )
Array ( [id] => 3 [abbr] => 003 [name] => Alan )
Array ( [id] => 4 [abbr] => 004 [name] => Alan )
Array ( [id] => 5 [abbr] => 005 [name] => Alan )
Array ( [id] => 6 [abbr] => 006 [name] => Alan )
Array ( [id] => 7 [abbr] => 007 [name] => Alan )
Array ( [id] => 8 [abbr] => 008 [name] => Alan )
Array ( [id] => 9 [abbr] => 009 [name] => Alan )
Array ( [id] => 10 [abbr] => 010 [name] => Alan )
Array ( [id] => 11 [abbr] => 011 [name] => Alan )
Array ( [id] => 12 [abbr] => 012 [name] => Alan )
Array ( [id] => 13 [abbr] => 013 

Re: [sqlite] SQLite Native in PHP in the future?

[Simon Slavin]

On 13 Jan 2013, at 10:17am, "Tracy Rohan"  wrote:

> Hi, I read that SQLite may not be native in PHP in the future?  When I say
> native, I mean that platform/devices will need to install SQLite as an
> extension before it will function (as it was previously).  Is this true?

You seem to know more about it than I do.  Where did you read this ?

There are actually three native implementations of SQLite in standard 
distributions of PHP.  One is a PDO module.  One is a thin implementation of 
the the SQLite3 library convenient for people who want to use SQLite3.  And the 
third is a thin implementation of an earlier and now outdated earlier SQLite 
library.  I think they could use the third of these without too many people 
complaining.  Maybe that's what your source means.

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

Re: [sqlite] insert into table problem

[Clemens Ladisch]

yanhong ye wrote:
>> insert into table3(name,mark,mdate) select name,mark,now() from table2
>> where  table2.name=table1.name;
>
> Not work, I don't know what's problem.

Did you actually read the error messages?

sqlite> create table table1(name);
sqlite> create table table2(name,mark);
sqlite> create table table3(name,mark,mdate);
sqlite> insert into table3(name,mark,mdate) select name,mark,now() from table2
   ...> where  table2.name=table1.name;
Error: no such function: now
sqlite> insert into table3(name,mark,mdate) select name,mark,current_timestamp 
from table2
   ...> where  table2.name=table1.name;
Error: no such column: table1.name

You have to add table1 to the FROM clause, if you want to have a join.
Or not.  Maybe you should execute "DROP TABLE table2;".  It's impossible
to know without you telling us what problem you want to solve.


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

[sqlite] SQLite Native in PHP in the future?

[Tracy Rohan]

Hi, I read that SQLite may not be native in PHP in the future?  When I say
native, I mean that platform/devices will need to install SQLite as an
extension before it will function (as it was previously).  Is this true?

Thank you,

Tracy 

 

 

 

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

[sqlite] insert into table problem

[yanhong ye]

>
>insert into table3(name,mark,mdate) select name,mark,now() from table2
>where  table2.name=table1.name;

Not work, I don't know what's problem.


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