Re: [sqlite] Theoretical write performance for low-throughput devices
On 10/21/19, Jonathan Brandmeyer wrote: > > No significant change. The target filesystem only caches non-aligned > writes, so there usually isn't anything for it to do on fsync anyway. > OK. I don't have any more ideas at the moment, and without access to your code, and your platform, I can't really debug it. But do please know that you should only be getting less than 2x writes. I suggest adding instrumentation and trying to come up with a simplified test case. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On Mon, Oct 21, 2019 at 5:00 PM Richard Hipp wrote: > > On 10/21/19, Jonathan Brandmeyer wrote: > > On Mon, Oct 21, 2019 at 10:31 AM Richard Hipp wrote: > >> > >> On 10/21/19, Jonathan Brandmeyer wrote: > >> > Or, how many times is each page written by SQLite for an insert-heavy > >> > test? The answer appears to be "4", but I can only account for two of > >> > those four. > >> > >> Are you measuring writes at the OS-interface layer, or writes at the > >> hardware layer? SQLite should issue no more than 2x writes in WAL > >> mode, and less if the same page is modified more than once. But who > >> knows how the OS might be multiplying this to accomplish its own > >> filesystem consistency. > >> > >> Consider compiling the > >> https://www.sqlite.org/src/file/ext/misc/vfslog.c extension into > >> SQLite on a workstation and running your code there, to get more > >> details about everything that SQLite is doing with storage. > > > > Thanks for the pointer. I didn't do this exactly, but instead > > instrumented SQLite's unixWrite() and my lowest-level NAND driver's > > block write function on the target hardware to capture summary > > counters of both events. The filesystem's own consistency overhead > > adds up to about 2% additional writes - not nearly enough to account > > for the missing 2x. The throughput implied by the low-level counters > > is consistent with the benchmark results. > > > > What happens if you set "PRAGMA synchronous=OFF". No significant change. The target filesystem only caches non-aligned writes, so there usually isn't anything for it to do on fsync anyway. > (I'm not suggesting > that you do this in production - it is just an experiment to try to > help figure out what is going on.) Understood. If it helps, we are doing a couple of other things that are dissimilar to a typical workstation configuration. We are using the builtin "unix-none" VFS, the -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 and -DSQLITE_DEFAULT_LOCKING_MODE=1 compile-time options, along with a design rules that there are zero or one database connection per file, and only one thread accesses that connection at a time. The target lacks mmap(), posix file locks, and multiple address spaces, so this seemed like the right settings to use. Thanks, -- Jonathan Brandmeyer PlanetiQ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On 10/21/19, Jonathan Brandmeyer wrote: > On Mon, Oct 21, 2019 at 10:31 AM Richard Hipp wrote: >> >> On 10/21/19, Jonathan Brandmeyer wrote: >> > Or, how many times is each page written by SQLite for an insert-heavy >> > test? The answer appears to be "4", but I can only account for two of >> > those four. >> >> Are you measuring writes at the OS-interface layer, or writes at the >> hardware layer? SQLite should issue no more than 2x writes in WAL >> mode, and less if the same page is modified more than once. But who >> knows how the OS might be multiplying this to accomplish its own >> filesystem consistency. >> >> Consider compiling the >> https://www.sqlite.org/src/file/ext/misc/vfslog.c extension into >> SQLite on a workstation and running your code there, to get more >> details about everything that SQLite is doing with storage. > > Thanks for the pointer. I didn't do this exactly, but instead > instrumented SQLite's unixWrite() and my lowest-level NAND driver's > block write function on the target hardware to capture summary > counters of both events. The filesystem's own consistency overhead > adds up to about 2% additional writes - not nearly enough to account > for the missing 2x. The throughput implied by the low-level counters > is consistent with the benchmark results. > What happens if you set "PRAGMA synchronous=OFF". (I'm not suggesting that you do this in production - it is just an experiment to try to help figure out what is going on.) -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On Mon, Oct 21, 2019 at 10:31 AM Richard Hipp wrote: > > On 10/21/19, Jonathan Brandmeyer wrote: > > Or, how many times is each page written by SQLite for an insert-heavy > > test? The answer appears to be "4", but I can only account for two of > > those four. > > Are you measuring writes at the OS-interface layer, or writes at the > hardware layer? SQLite should issue no more than 2x writes in WAL > mode, and less if the same page is modified more than once. But who > knows how the OS might be multiplying this to accomplish its own > filesystem consistency. > > Consider compiling the > https://www.sqlite.org/src/file/ext/misc/vfslog.c extension into > SQLite on a workstation and running your code there, to get more > details about everything that SQLite is doing with storage. Thanks for the pointer. I didn't do this exactly, but instead instrumented SQLite's unixWrite() and my lowest-level NAND driver's block write function on the target hardware to capture summary counters of both events. The filesystem's own consistency overhead adds up to about 2% additional writes - not nearly enough to account for the missing 2x. The throughput implied by the low-level counters is consistent with the benchmark results. Thanks, -- Jonathan Brandmeyer PlanetiQ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Roadmap?
On 10/20/19 11:03 PM, Rowan Worth wrote: > On Sun, 20 Oct 2019 at 17:04, Simon Slavin wrote: > >> Another common request is full support for Unicode (searching, sorting, >> length()). But even just the tables required to identify character >> boundaries are huge. >> > Nitpick: there are no tables required to identify character boundaries. For > utf-8 you know if there's another byte to come which is part of the current > codepoint based on whether the current byte's high bit is set, and > furthermore you know how many bytes to expect based on the initial byte. > > I'm less familiar with utf-16 which SQLite has some support for, but a > quick read suggests there are exactly two reserved bit patterns you need to > care about to identify surrogate pairs and thus codepoint boundaries. > > Tables relating to collation order, character case, and similar codepoint > data can of course get huge, so your point stands. > -Rowan My memory is that Unicode is somewhat careful NOT to define what is a 'character' because that can really get complicated, and often application specific about what it wants. You have code-units, which for utf-8 are basically bytes. You have code-points, which is what most people think of as a 'character' which has a single Unicode Codepoint number. Then you have Graphemes, which are clusters of code-points that tend to be expressed in a single glyph in output. (and some code-points don't generate any output). Dealing with Graphemes gets complicated, and that is where you run into the need for lots of tables. Code-points them selves are fairly simple to deal with, the problem is that in some langauges just dealing with code-points doesn't let you fully handle some of the 'simple' operations like sorting, or case folding with 100% accuracy, that sometimes requires dealing with code-point clusters. But, you also run into the issue (as I understand it) that Unicode doesn't really define a universal ordering for all characters, that this can be a language specific problem, and Unicode can't really solve that issue. (Two langauges might use some of the same characters, but treat them differently for sorting). -- Richard Damon ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Theoretical write performance for low-throughput devices
On 10/21/19, Jonathan Brandmeyer wrote: > Or, how many times is each page written by SQLite for an insert-heavy > test? The answer appears to be "4", but I can only account for two of > those four. Are you measuring writes at the OS-interface layer, or writes at the hardware layer? SQLite should issue no more than 2x writes in WAL mode, and less if the same page is modified more than once. But who knows how the OS might be multiplying this to accomplish its own filesystem consistency. Consider compiling the https://www.sqlite.org/src/file/ext/misc/vfslog.c extension into SQLite on a workstation and running your code there, to get more details about everything that SQLite is doing with storage. -- D. Richard Hipp d...@sqlite.org ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Theoretical write performance for low-throughput devices
Or, how many times is each page written by SQLite for an insert-heavy test? The answer appears to be "4", but I can only account for two of those four. I'm working on an embedded system that uses a log-structured filesystem on raw NAND flash. This is not your typical workstation's managed flash (SATA/NVMe), or portable managed flash (SD/USB). It's a bare-nekkid ONFI-speaking chip. All reads and writes are one 2kB page at a time. There is no readahead, and no write buffering by the driver or filesystem for page-sized writes. We got the following performance numbers out of the flash storage: Streaming reads through the filesystem: 7.5 MB/s. Streaming writes through the filesystem: 5.4 MB/s. Single insert performance through SQLite: 0.2 MB/s. Bulk insert performance through SQLIte: 1.3 MB/s, asymptotic for very large transactions. I do expect the single-insert performance to be much lower than the bulk insert performance. We are using the WAL, and this benchmark includes the cost of a checkpoint at the end. Futziing with the WAL autocheckpoint size has little impact for smaller WAL sizes. My working assumption is that using the WAL for an insertion-heavy workload would consume roughly half of my available throughput due to checkpoint writes. Indeed, if the autocheckpoint level is raised high enough that the entire benchmark fits in the WAL, then I do observe that the write throughput asymptotically approaches 2.6 MB/s instead of 1.3 MB/s. That leaves one more factor of two somewhere. The table in question has the schema: ``` CREATE TABLE IF NOT EXISTS `chunks` ( `target_id`INTEGER NOT NULL, `chunk_num`INTEGER NOT NULL, `chunk_blob`BLOB NOT NULL, PRIMARY KEY(`target_id`,`chunk_num`) ); ``` Other factors that might help understand our workload: Blobs are a little less than 1 kB each, and we're using the default DB page size (4 kB). So I would expect that SQLite would pack about 3 rows per page, leaving some extra for primary keys, field delimiters and other metadata. I understand that the composite primary key implies an index table that goes with the blobs, which implies some inherent write amplification to account for the index. Still, my expectation is that the write throughput added by the index should be close to the size of the key columns, not the blob columns. So 2x still seems too high. Any other ideas? Thanks, -- Jonathan Brandmeyer ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] ICC Compiler Warning
Hello,
compiling the latest stable version 3300100 using VS2019 on Windows and ICC
2019 Update 5 gives this warning:
1>-- Rebuild All started: Project: SQLite DLL, Configuration: Release x64
--
1>sqlite3.c
1>C:\Dropbox\Boo\Shares\Code\SQLite\SQLite DLL\sqlite3.c(154619): message #111:
statement is unreachable
1>return 0;
1>^
1>
Thats related to this code block:
/*
** Return the fallback token corresponding to canonical token iToken, or
** 0 if iToken has no fallback.
*/
SQLITE_PRIVATE int sqlite3ParserFallback(int iToken){
#ifdef YYFALLBACK
assert( iToken<(int)(sizeof(yyFallback)/sizeof(yyFallback[0])) );
return yyFallback[iToken];
#else
(void)iToken;
#endif
return 0;
}
Sent with [ProtonMail](https://protonmail.com) Secure Email.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Problem saving datatable back to the sqlite database
Hello,
I would appreciate any help in finding why I get an error when saving back
to a database.
I am using vb.net in VS-2019 with sqlite.
The error is: "Concurrency violation: the UpdateCommand affected 0 of the
expected 1 records."
I have worked out that the error only occurs when my table has a date field
which I fill from the underlying database use a sqlitedatadapter as follows:
Public connstring As String = "Data
Source=c:\VS-2019\SaveTable\SaveTable\Data\TestDb.db"
Public conn As New SQLiteConnection
Public Sub FillMainTables()
conn.ConnectionString = connstring
conn.Open()
DTAllCustomers.Rows.Clear()
sql = "SELECT * FROM Customers"
AllCustomersAdapter = New SQLiteDataAdapter(sql, conn)
AllCustomersAdapter.Fill(DTAllCustomers)
End Sub
I then modify a few records in the datatable(DTAllCustomers) using this
code:
For i = 0 To DTAllCustomers.Rows.Count - 1
If DTAllCustomers.Rows(i).Item("sitecode") = "RESI" Then
DTAllCustomers.Rows(i).Item("Email") = "Changed"
End If
Next
I then try to save the modified table back to the underlying database with:
Dim cbAllCustomers As New SQLiteCommandBuilder(AllCustomersAdapter)
AllCustomersAdapter.UpdateCommand = cbAllCustomers.GetUpdateCommand
Try
AllCustomersAdapter.Update(DTAllCustomers)
Catch ex As Exception
MsgBox(ex.Message)
End Try
When I remove the DATETIME columns in DTAllCustomers the error disappears
and the update completes correctly. Does SQLite change the date
structure/value of the DateTime columns between filling the datatable and
saving back to the datatable?
I created the database using "sqlite expert personal". When I loop through
the DTAllCustomers table I inspect the value of a date field and it appears
as - #2/12/2020 12:00:00 AM#
Any help is greatly appreciated as I have spent weeks trying to solve this
through any number of forums.
Brad
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
> Not a clue. I didn't write the schema. I know, I asked the OP. RBS On Mon, Oct 21, 2019 at 4:16 PM Keith Medcalf wrote: > > On Monday, 21 October, 2019 08:47, Bart Smissaert < > bart.smissa...@gmail.com> wrote: > > >Shouldn't there be field MODE_ID in the Employee table? > > Not a clue. I didn't write the schema. Perhaps there is another table > called MODATES that has a link to the employee (EMPLOYEE_ID) and a link to > the mode (MODE_ID) together with the start and end dates to which that > linkage applies. And yet another table that called CIDATES that has a link > the employee (EMPLOYEE_ID) and to the city (CITY_ID) together with the > start and end dates to which that linkage applies. (In which case there is > still no solution since the problem is inadequately stated. > > Also, what about the people that walk except when it is raining and then > they take the bus unless in either case they wake up late and drive > themselves in their own car. Except of course on Tuesday when the > neighbour is going to the supermarket at the same time as they happen to be > going to work so they hitch a ride with the neighbour in the neighbours car > so they can smoke a phatty on the way, unless it is the second Tuesday of > the month in which case they ride their bike because it is "Patch Tuesday" > and, you know, just to be safe. Except of course if there was an > earthquake in which case they just stay home. > > The point being that there is no way to solve the problem stated using the > given information, and no way to correct for the missing information since > one has no clue what it is or how complicated it is. > > >On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf > wrote: > > >> On Monday, 21 October, 2019 08:31, Winfried > wrote: > >> > >> >Using the following tables, I need to find how employees from each > >> city come to work. > >> > >> >== Employees table: > >> >EMPLOYEE_ID | CITY_ID > >> >Cities table: > >> >CITY_ID | CITY_TXT > >> >Mode table: > >> >MODE_ID | MODE_TXT > >> > > >> >This is the type of output I need to get ultimately: > >> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER > >> >City1 | 15% | 5% | 50% [ 25% | 5% > >> > >> No amount of queries or magical incantations will get you the results > >you > >> are asking for because there is no way to get from MODE_TXT to > >CITY_TXT. > >> That is, there is no way to compose the rest of the query as indicated > >by > >> the ... to obtain the data required to solve the problem: > >> > >> select EMPLOYEE_ID, CITY_TXT, MODE_TXT > >> from EMPLOYEES, CITIES, MODE > >> where > >> > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
On Monday, 21 October, 2019 08:47, Bart Smissaert wrote: >Shouldn't there be field MODE_ID in the Employee table? Not a clue. I didn't write the schema. Perhaps there is another table called MODATES that has a link to the employee (EMPLOYEE_ID) and a link to the mode (MODE_ID) together with the start and end dates to which that linkage applies. And yet another table that called CIDATES that has a link the employee (EMPLOYEE_ID) and to the city (CITY_ID) together with the start and end dates to which that linkage applies. (In which case there is still no solution since the problem is inadequately stated. Also, what about the people that walk except when it is raining and then they take the bus unless in either case they wake up late and drive themselves in their own car. Except of course on Tuesday when the neighbour is going to the supermarket at the same time as they happen to be going to work so they hitch a ride with the neighbour in the neighbours car so they can smoke a phatty on the way, unless it is the second Tuesday of the month in which case they ride their bike because it is "Patch Tuesday" and, you know, just to be safe. Except of course if there was an earthquake in which case they just stay home. The point being that there is no way to solve the problem stated using the given information, and no way to correct for the missing information since one has no clue what it is or how complicated it is. >On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf wrote: >> On Monday, 21 October, 2019 08:31, Winfried wrote: >> >> >Using the following tables, I need to find how employees from each >> city come to work. >> >> >== Employees table: >> >EMPLOYEE_ID | CITY_ID >> >Cities table: >> >CITY_ID | CITY_TXT >> >Mode table: >> >MODE_ID | MODE_TXT >> > >> >This is the type of output I need to get ultimately: >> >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER >> >City1 | 15% | 5% | 50% [ 25% | 5% >> >> No amount of queries or magical incantations will get you the results >you >> are asking for because there is no way to get from MODE_TXT to >CITY_TXT. >> That is, there is no way to compose the rest of the query as indicated >by >> the ... to obtain the data required to solve the problem: >> >> select EMPLOYEE_ID, CITY_TXT, MODE_TXT >> from EMPLOYEES, CITIES, MODE >> where >> -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
> Shouldn't there be field MODE_ID in the Employee table? > It looks like a high school homework to me :D ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
Shouldn't there be field MODE_ID in the Employee table? RBS On Mon, Oct 21, 2019 at 3:44 PM Keith Medcalf wrote: > > On Monday, 21 October, 2019 08:31, Winfried wrote: > > >Using the following tables, I need to find how employees from each city > >come to work. > > >== Employees table: > >EMPLOYEE_ID | CITY_ID > >Cities table: > >CITY_ID | CITY_TXT > >Mode table: > >MODE_ID | MODE_TXT > > > >This is the type of output I need to get ultimately: > >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER > >City1 | 15% | 5% | 50% [ 25% | 5% > > No amount of queries or magical incantations will get you the results you > are asking for because there is no way to get from MODE_TXT to CITY_TXT. > That is, there is no way to compose the rest of the query as indicated by > the ... to obtain the data required to solve the problem: > > select EMPLOYEE_ID, CITY_TXT, MODE_TXT > from EMPLOYEES, CITIES, MODE > where > > -- > The fact that there's a Highway to Hell but only a Stairway to Heaven says > a lot about anticipated traffic volume. > > > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Can SQLite do this in a single query?
On Monday, 21 October, 2019 08:31, Winfried wrote: >Using the following tables, I need to find how employees from each city >come to work. >== Employees table: >EMPLOYEE_ID | CITY_ID >Cities table: >CITY_ID | CITY_TXT >Mode table: >MODE_ID | MODE_TXT > >This is the type of output I need to get ultimately: >CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER >City1 | 15% | 5% | 50% [ 25% | 5% No amount of queries or magical incantations will get you the results you are asking for because there is no way to get from MODE_TXT to CITY_TXT. That is, there is no way to compose the rest of the query as indicated by the ... to obtain the data required to solve the problem: select EMPLOYEE_ID, CITY_TXT, MODE_TXT from EMPLOYEES, CITIES, MODE where -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Can SQLite do this in a single query?
Hello, Before I dive into https://www.sqlitetutorial.net, I'd like to check with experienced users whether SQLite is up to the task, or I should maybe run multiple queries possibly with some help from PHP. Using the following tables, I need to find how employees from each city come to work. == Employees table: EMPLOYEE_ID | CITY_ID Cities table: CITY_ID | CITY_TXT Mode table: MODE_ID | MODE_TXT This is the type of output I need to get ultimately: CITY | WALKING | CYCLING | PUBLIC TRANSIT | CAR | OTHER City1 | 15% | 5% | 50% [ 25% | 5% Do you think it can it be done in a single query using a mixture of COUNT(), SUM(), GROUP BY, HAVING etc., or will I have to split the task into a few independent queries? Thank you. -- Sent from: http://sqlite.1065341.n5.nabble.com/ ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PHP+PDO] SELECT fails when using prepare + placeholder
Zakrzewski, Jakub wrote
> It's because `execute()` returns a boolean (as stated in the manual
> https://www.php.net/manual/en/pdostatement.execute.php ). `fetch()` must
> be called on `$query`
Thanks much!
=
$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=? LIMIT 1");
$query->execute(array($mode));
$result = $query->fetch(PDO::FETCH_ASSOC);
if($result){
print($result["mode_txt"] . "");
} else
print("Bad!");
=
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [PHP+PDO] SELECT fails when using prepare + placeholder
It's because `execute()` returns a boolean (as stated in the manual
https://www.php.net/manual/en/pdostatement.execute.php ).
`fetch()` must be called on `$query`
From: sqlite-users on behalf of
Winfried
Sent: 21 October 2019 11:47
To: sqlite-users@mailinglists.sqlite.org
Subject: [sqlite] [PHP+PDO] SELECT fails when using prepare + placeholder
Hello,
I can't figure out why PHP isn't happy with the following query:
==
prepare('INSERT INTO mytable (date, home_id, mode_id) VALUES
(?, ?, ?)');
$query->execute(array($today,$home, $mode));
//CREATE TABLE mode (mode_id INTEGER, mode_txt TEXT);
//SELECT OK
//$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=0 LIMIT
1");
//NOK !
$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=? LIMIT 1");
$result=$query->execute(array($mode));
if ($result) {
//! Fatal error: Call to a member function fetch() on boolean in
blah.php
$mode = $result->fetch(PDO::FETCH_COLUMN);
}
$dbh = null;
?>
==
Is it due to some incompatibility between SQLite's INTEGER and PDO?
Thank you.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] [PHP+PDO] SELECT fails when using prepare + placeholder
Hello,
I can't figure out why PHP isn't happy with the following query:
==
prepare('INSERT INTO mytable (date, home_id, mode_id) VALUES
(?, ?, ?)');
$query->execute(array($today,$home, $mode));
//CREATE TABLE mode (mode_id INTEGER, mode_txt TEXT);
//SELECT OK
//$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=0 LIMIT
1");
//NOK !
$query = $dbh->prepare("SELECT mode_txt FROM mode WHERE mode_id=? LIMIT 1");
$result=$query->execute(array($mode));
if ($result) {
//! Fatal error: Call to a member function fetch() on boolean in
blah.php
$mode = $result->fetch(PDO::FETCH_COLUMN);
}
$dbh = null;
?>
==
Is it due to some incompatibility between SQLite's INTEGER and PDO?
Thank you.
--
Sent from: http://sqlite.1065341.n5.nabble.com/
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: select for power-meter accumulated total readings
> > For a (meaningful, as opposed to "all interpolated values") granularity of > 1 hour, information theory states that you need a sample every 30 minutes > or less. > > The desire to charge consumers more for "peak power" (just like utilities > have to pay more for "peak power" and less for "base load") is the driving > force behind the installation of "smart meters". Currently, consumers pay > "flat rate" based on their total annual consumption, irrespective of their > contribution to bas and peak loads. > > Yes, I understand this. But I need this for my own facility :D ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: select for power-meter accumulated total readings
On Monday, 21 October, 2019 00:49, Hick Gunter wrote: >The desire to charge consumers more for "peak power" (just like utilities >have to pay more for "peak power" and less for "base load") is the >driving force behind the installation of "smart meters". This is the propaganda behind "smart meters" but it is not the reality. So called "smart meters" are not "smart" enough to charge based on spot market pricing and they never will be, because that would permit consumers to optimize their energy use, and that is not the goal. This is and remains within the remit of only the largest industrial power consumers only -- those who can afford to flick the parasites into the dustbin without consequence. >Currently, consumers pay "flat rate" based on their total annual >consumption, irrespective of their contribution to bas and peak loads. The flat rate is the weighted average of the usage by the load over a period of time. What consumers' pay is based on that weighted average pricing and not based on actual price. "Smart Meters" merely divide the weighted average periods into somewhat smaller time-based weighted averages, but it is still based on the weighted average and not on actual price at the time of use. It is entirely possible with current technology to meter and bill based entirely on spot price, but this is not done because it would make obvious to everyone involved who was lining their pockets with other people's money for doing nothing, and those wearing the pants who's pockets are being lined do not want this. In fact, there are even intermediaries who claim to be able to provide "better" flat rate pricing on contract than time-of-use metering permits. This is clearly impossible or they would be bankrupt. So obviously the whole shenanigans must be considerably more expensive for the consumer than reality in order for the parasites to stay in business. Now back to out regularly scheduled programming ... -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Re: select for power-meter accumulated total readings
>What about if I want 1 hour granity? (to plot a graph of daily consumption for >example) For a (meaningful, as opposed to "all interpolated values") granularity of 1 hour, information theory states that you need a sample every 30 minutes or less. The desire to charge consumers more for "peak power" (just like utilities have to pay more for "peak power" and less for "base load") is the driving force behind the installation of "smart meters". Currently, consumers pay "flat rate" based on their total annual consumption, irrespective of their contribution to bas and peak loads. ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [EXTERNAL] Roadmap?
The "virtual table playground gadget" was our primary reason for selecting SQLite in the first place, because none of our production data sources are native SQLite tables. Instead, we have about 20 virtual table modules that implement about 1000 virtual table instances. -Ursprüngliche Nachricht- Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von Thomas Kurz Gesendet: Sonntag, 20. Oktober 2019 09:53 An: SQLite mailing list Betreff: [EXTERNAL] [sqlite] Roadmap? I'd kindly ask whether there is some sort of roadmap for SQLite development? Someone recently pointed out how much he loves the "lite" and well-thought features. I cannot see that: I observe that many "playground" gadgets keep being implemented (like virtual columns, virtual tables, FTS3/4/5, ...), where one might wonder about their relationship to "Liteness", whereas other features, essential basics of the SQL standards, are still missing and there is no indication they are to be added. Without wanting to offend someone, I cannot see the logic in development, so: Is there some kind of roadmap? ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0 May be privileged. May be confidential. Please delete if not the addressee. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
