Re: [sqlite] Inmemory database in sqlite
Is features like WAL (https://www.sqlite.org/wal.html) not available for in memory databases ? Thanks Prakash On Fri, Oct 17, 2014 at 11:23 AM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: Today, random-access memory takes the form of integrated circuits http://en.m.wikipedia.org/wiki/Integrated_circuit. RAM is normally associated with volatile http://en.m.wikipedia.org/wiki/Volatile_memory types of memory (such as DRAM http://en.m.wikipedia.org/wiki/DRAM memory modules http://en.m.wikipedia.org/wiki/DIMM), where stored information is lost if the power is removed, although many efforts have been made to develop non-volatile RAM chips. Source: Wikipedia Il 17/ott/2014 11:48 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Does in memory database in sqlite have journal files associated with it ? If there's a system failure before an in memory database is backed up ? Will there be data loss ? Or Can you kindly tell me how sqlite handles this ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
Hi, Let's take the case of MemSQL for example. It is an in memory database and it supports durability: Link : http://developers.memsql.com/docs/3.1/faq.html#c3-q1 And Oracle's In memory db TimesTen also provide durability: Link : http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21631/overview.htm#TTCIN129 Does sqlite's in memory db have this feature ? Thanks Prakash On Fri, Oct 17, 2014 at 11:54 AM, Prakash Premkumar prakash.p...@gmail.com wrote: Is features like WAL (https://www.sqlite.org/wal.html) not available for in memory databases ? Thanks Prakash On Fri, Oct 17, 2014 at 11:23 AM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: Today, random-access memory takes the form of integrated circuits http://en.m.wikipedia.org/wiki/Integrated_circuit. RAM is normally associated with volatile http://en.m.wikipedia.org/wiki/Volatile_memory types of memory (such as DRAM http://en.m.wikipedia.org/wiki/DRAM memory modules http://en.m.wikipedia.org/wiki/DIMM), where stored information is lost if the power is removed, although many efforts have been made to develop non-volatile RAM chips. Source: Wikipedia Il 17/ott/2014 11:48 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Does in memory database in sqlite have journal files associated with it ? If there's a system failure before an in memory database is backed up ? Will there be data loss ? Or Can you kindly tell me how sqlite handles this ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
if it uses disk, it can be persistant. If it's in memory, it's only as reliable as the power to the computer. If it's in memory and cached to disk, it's really a disk database. Just because someone says 'we have a memory database, that's the primary and disk is secondary' it's still a disk database. On Fri, Oct 17, 2014 at 12:14 AM, Prakash Premkumar prakash.p...@gmail.com wrote: Hi, Let's take the case of MemSQL for example. It is an in memory database and it supports durability: Link : http://developers.memsql.com/docs/3.1/faq.html#c3-q1 And Oracle's In memory db TimesTen also provide durability: Link : http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21631/overview.htm#TTCIN129 Does sqlite's in memory db have this feature ? Thanks Prakash On Fri, Oct 17, 2014 at 11:54 AM, Prakash Premkumar prakash.p...@gmail.com wrote: Is features like WAL (https://www.sqlite.org/wal.html) not available for in memory databases ? Thanks Prakash On Fri, Oct 17, 2014 at 11:23 AM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: Today, random-access memory takes the form of integrated circuits http://en.m.wikipedia.org/wiki/Integrated_circuit. RAM is normally associated with volatile http://en.m.wikipedia.org/wiki/Volatile_memory types of memory (such as DRAM http://en.m.wikipedia.org/wiki/DRAM memory modules http://en.m.wikipedia.org/wiki/DIMM), where stored information is lost if the power is removed, although many efforts have been made to develop non-volatile RAM chips. Source: Wikipedia Il 17/ott/2014 11:48 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Does in memory database in sqlite have journal files associated with it ? If there's a system failure before an in memory database is backed up ? Will there be data loss ? Or Can you kindly tell me how sqlite handles this ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] strftime() issues
Hi all
Trying to use strftime() to extract current Year-Month seems to go nuts. Any
ideas?
$ sqlite3
SQLite version 3.6.20
Enter .help for instructions
Enter SQL statements terminated with a ;
sqlite select strftime('%s', 'now');
1413536061
sqlite -- fine, but
sqlite select strftime('%Y-%m', strftime('%s', 'now'));
3865-46
sqlite -- ?!??
sqlite .quit
$
--
Vennlige hilsener / Best regards
roy
--
Roy Sigurd Karlsbakk
(+47) 98013356
r...@karlsbakk.net
http://blogg.karlsbakk.net/
GPG Public key: http://karlsbakk.net/roysigurdkarlsbakk.pubkey.txt
--
I all pedagogikk er det essensielt at pensum presenteres intelligibelt. Det er
et elementært imperativ for alle pedagoger å unngå eksessiv anvendelse av
idiomer med xenotyp etymologi. I de fleste tilfeller eksisterer adekvate og
relevante synonymer på norsk.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] strftime() issues
Roy Sigurd Karlsbakk wrote:
Trying to use strftime() to extract current Year-Month seems to go nuts.
sqlite select strftime('%s', 'now');
1413536061
sqlite select strftime('%Y-%m', strftime('%s', 'now'));
3865-46
SQLite interprets a number as a Julian day number. To have it
interpreted as second requires the unixepoch modifier:
sqlite select strftime('%Y-%m', strftime('%s', 'now'), 'unixepoch');
2014-10
Anyway, there's no reason to call strftime() twice:
sqlite select strftime('%Y-%m', 'now');
2014-10
Regards,
Clemens
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
From memsql site: These features can be tuned all the way from synchronous durability (every write transaction is recorded on disk before the query completes) to purely in-memory durability (maximum sustained throughput on writes). From sqlite website: The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt. So you can go all the ways from no acid (data and journal in ram), to aci database (syncronous=normal) to fully acid. If you increase cache size enough you'll have the same as a in memory database Il 17/ott/2014 14:15 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Let's take the case of MemSQL for example. It is an in memory database and it supports durability: Link : http://developers.memsql.com/docs/3.1/faq.html#c3-q1 And Oracle's In memory db TimesTen also provide durability: Link : http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21631/overview.htm#TTCIN129 Does sqlite's in memory db have this feature ? Thanks Prakash On Fri, Oct 17, 2014 at 11:54 AM, Prakash Premkumar prakash.p...@gmail.com wrote: Is features like WAL (https://www.sqlite.org/wal.html) not available for in memory databases ? Thanks Prakash On Fri, Oct 17, 2014 at 11:23 AM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: Today, random-access memory takes the form of integrated circuits http://en.m.wikipedia.org/wiki/Integrated_circuit. RAM is normally associated with volatile http://en.m.wikipedia.org/wiki/Volatile_memory types of memory (such as DRAM http://en.m.wikipedia.org/wiki/DRAM memory modules http://en.m.wikipedia.org/wiki/DIMM), where stored information is lost if the power is removed, although many efforts have been made to develop non-volatile RAM chips. Source: Wikipedia Il 17/ott/2014 11:48 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Does in memory database in sqlite have journal files associated with it ? If there's a system failure before an in memory database is backed up ? Will there be data loss ? Or Can you kindly tell me how sqlite handles this ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
Thanks Alessandro. So the approach where we open the db with :memory: keyword does not provide durability . Only by increasing the cache size can we make db act as an inmemory db with durablity. That's the conclusion right ? Thanks Prakash On Fri, Oct 17, 2014 at 2:53 PM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: From memsql site: These features can be tuned all the way from synchronous durability (every write transaction is recorded on disk before the query completes) to purely in-memory durability (maximum sustained throughput on writes). From sqlite website: The MEMORY journaling mode stores the rollback journal in volatile RAM. This saves disk I/O but at the expense of database safety and integrity. If the application using SQLite crashes in the middle of a transaction when the MEMORY journaling mode is set, then the database file will very likely go corrupt. So you can go all the ways from no acid (data and journal in ram), to aci database (syncronous=normal) to fully acid. If you increase cache size enough you'll have the same as a in memory database Il 17/ott/2014 14:15 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Let's take the case of MemSQL for example. It is an in memory database and it supports durability: Link : http://developers.memsql.com/docs/3.1/faq.html#c3-q1 And Oracle's In memory db TimesTen also provide durability: Link : http://docs.oracle.com/cd/E21901_01/doc/timesten.1122/e21631/overview.htm#TTCIN129 Does sqlite's in memory db have this feature ? Thanks Prakash On Fri, Oct 17, 2014 at 11:54 AM, Prakash Premkumar prakash.p...@gmail.com wrote: Is features like WAL (https://www.sqlite.org/wal.html) not available for in memory databases ? Thanks Prakash On Fri, Oct 17, 2014 at 11:23 AM, Alessandro Marzocchi alessandro.marzoc...@gmail.com wrote: Today, random-access memory takes the form of integrated circuits http://en.m.wikipedia.org/wiki/Integrated_circuit. RAM is normally associated with volatile http://en.m.wikipedia.org/wiki/Volatile_memory types of memory (such as DRAM http://en.m.wikipedia.org/wiki/DRAM memory modules http://en.m.wikipedia.org/wiki/DIMM), where stored information is lost if the power is removed, although many efforts have been made to develop non-volatile RAM chips. Source: Wikipedia Il 17/ott/2014 11:48 Prakash Premkumar prakash.p...@gmail.com ha scritto: Hi, Does in memory database in sqlite have journal files associated with it ? If there's a system failure before an in memory database is backed up ? Will there be data loss ? Or Can you kindly tell me how sqlite handles this ? Thanks Prakash ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users ___ 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] group_concat query performance
Joe Mistachkin wrote:
Thanks for the query. It's difficult to track down performance issues with
System.Data.SQLite without seeing the C# (or VB.NET) example code as there are
a variety of ways to query and process data using it.
Is there any chance we could see the code that is using System.Data.SQLite?
Yes, sure. Here they are, the first one is C++, the second one VB.net .Net
Framework 4
With regards,
Peter
C++ example code:
#include stdafx.h
#include iostream
#include string
#include sqlite3.h
#include time.h
int main()
{
sqlite3 *db;
sqlite3_stmt *res;
time_t execStart, execStop;
const char *errMSG;
const char *tail;
std::cout Running GROUP_CONCAT function test... std::endl;
time(execStart);
int error = sqlite3_open(test.db,db);
if (error)
{
std::cout Could not open DB std::endl;
sqlite3_close(db);
system(Pause);
return 1;
}
int cnt;
for (cnt = 0; cnt 5; cnt++)
{
std::string query = SELECT
GROUP_CONCAT(Parent.fkintFolderID,':') FilePath FROM tblFolderNestedSets Node,
tblFolderNestedSets Parent
WHERE Node.intLeft BETWEEN
Parent.intLeft AND Parent.intRight AND Parent.fkintSessionID =
Node.fkintSessionID
AND Node.fkintSessionID =
1824 AND Node.fkintFolderID = 2913318;;
error = sqlite3_prepare_v2(db,query.c_str(), query.length(),
res, tail);
if (error != SQLITE_OK)
{
std::cout Could not prepare sql std::endl;
sqlite3_close(db);
system(Pause);
return 1;
}
}
sqlite3_finalize(res);
sqlite3_close(db);
time(execStop);
double timeDiff = difftime(execStart, execStop);
printf(Elapsed time is %.2lf seconds. , timeDiff);
system(Pause);
return 0;
}
System.Data.SQLite example code:
Module Module1
Sub Main()
Dim _stopwatch As New Stopwatch()
Dim _dbConn As New System.Data.SQLite.SQLiteConnection()
Dim _dbPath As String = Data Source=test.db
_stopwatch.Start()
Console.WriteLine(Running GROUP_CONCAT function test...)
_dbConn.ConnectionString = _dbPath
_dbConn.Open()
Dim _selCmd As System.Data.SQLite.SQLiteCommand
_selCmd = New System.Data.SQLite.SQLiteCommand(_dbConn)
_selCmd.CommandText = SELECT GROUP_CONCAT(Parent.fkintFolderID,':')
FilePath FROM tblFolderNestedSets Node, tblFolderNestedSets Parent _
WHERE Node.intLeft BETWEEN Parent.intLeft AND
Parent.intRight AND Parent.fkintSessionID = Node.fkintSessionID _
AND Node.fkintSessionID = 1824 AND
Node.fkintFolderID = 2913318;
Dim _cnt As Integer
Dim _result As String
For _cnt = 1 To 5
_result = _selCmd.ExecuteScalar().ToString()
Next
_dbConn.Close()
_stopwatch.Stop()
Console.WriteLine(Elapsed time is {0} seconds., _stopwatch.Elapsed)
Console.WriteLine(Press any key to continue...)
Console.ReadKey()
End Sub
End Module
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat query performance
-Original Message-
From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users-
boun...@sqlite.org] On Behalf Of Kraijenbrink - FixHet - Systeembeheer
Sent: vrijdag 17 oktober 2014 12:01
To: General Discussion of SQLite Database
Subject: Re: [sqlite] group_concat query performance
Joe Mistachkin wrote:
Thanks for the query. It's difficult to track down performance issues
with
System.Data.SQLite without seeing the C# (or VB.NET) example code as
there are a variety of ways to query and process data using it.
Is there any chance we could see the code that is using
System.Data.SQLite?
Yes, sure. Here they are, the first one is C++, the second one VB.net .Net
Framework 4
Where do you perform the query in the C++ code?
Your C++ program shows how you prepare the statement 5000 times, but not how
you execute it.
The VB.Net code prepares the statement once, and then executes it 5000
times.
It looks like you are testing completely different things.
Bert
With regards,
Peter
C++ example code:
#include stdafx.h
#include iostream
#include string
#include sqlite3.h
#include time.h
int main()
{
sqlite3 *db;
sqlite3_stmt *res;
time_t execStart, execStop;
const char *errMSG;
const char *tail;
std::cout Running GROUP_CONCAT function test... std::endl;
time(execStart);
int error = sqlite3_open(test.db,db);
if (error)
{
std::cout Could not open DB std::endl;
sqlite3_close(db);
system(Pause);
return 1;
}
int cnt;
for (cnt = 0; cnt 5; cnt++)
{
std::string query = SELECT
GROUP_CONCAT(Parent.fkintFolderID,':') FilePath FROM
tblFolderNestedSets Node, tblFolderNestedSets Parent
WHERE Node.intLeft
BETWEEN Parent.intLeft AND Parent.intRight AND Parent.fkintSessionID =
Node.fkintSessionID
AND Node.fkintSessionID
=
1824 AND Node.fkintFolderID = 2913318;;
error = sqlite3_prepare_v2(db,query.c_str(), query.length(),
res, tail);
if (error != SQLITE_OK)
{
std::cout Could not prepare sql std::endl;
sqlite3_close(db);
system(Pause);
return 1;
}
}
sqlite3_finalize(res);
sqlite3_close(db);
time(execStop);
double timeDiff = difftime(execStart, execStop);
printf(Elapsed time is %.2lf seconds. , timeDiff);
system(Pause);
return 0;
}
System.Data.SQLite example code:
Module Module1
Sub Main()
Dim _stopwatch As New Stopwatch()
Dim _dbConn As New System.Data.SQLite.SQLiteConnection()
Dim _dbPath As String = Data Source=test.db
_stopwatch.Start()
Console.WriteLine(Running GROUP_CONCAT function test...)
_dbConn.ConnectionString = _dbPath
_dbConn.Open()
Dim _selCmd As System.Data.SQLite.SQLiteCommand
_selCmd = New System.Data.SQLite.SQLiteCommand(_dbConn)
_selCmd.CommandText = SELECT
GROUP_CONCAT(Parent.fkintFolderID,':') FilePath FROM
tblFolderNestedSets Node, tblFolderNestedSets Parent _
WHERE Node.intLeft BETWEEN Parent.intLeft
AND
Parent.intRight AND Parent.fkintSessionID = Node.fkintSessionID _
AND Node.fkintSessionID = 1824 AND
Node.fkintFolderID =
2913318;
Dim _cnt As Integer
Dim _result As String
For _cnt = 1 To 5
_result = _selCmd.ExecuteScalar().ToString()
Next
_dbConn.Close()
_stopwatch.Stop()
Console.WriteLine(Elapsed time is {0} seconds.,
_stopwatch.Elapsed)
Console.WriteLine(Press any key to continue...)
Console.ReadKey()
End Sub
End Module
___
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] Granularity of Locks in sqlite
On Thu, 16 Oct 2014 18:46:19 +0530, Prakash Premkumar prakash.p...@gmail.com wrote: Hi, From what I understand from reading the followig doc: http://www.sqlite.org/lockingv3.html sqlite supports only file level locking. Correct. Is there any attempts to improve the granularity of locking to table level or row level ? Considering the introduction of http://www.sqlite.org/whentouse.html , SQLite does not target use cases where a finer granularity would be useful, so I wouldn't expect any attempts to change that behaviour. Typically, embedded database libraries don't (have to) care about concurrency at all, so the concurrency that SQLite supports is a lot already. There are enough other products available that target that market. Thanks a lot. You're welcome. -- Regards, Kees Nuyt ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] group_concat query performance
Thanks Bert, You are right. Now the C++ example runs equaly fast. Looks like I have to redesign the Db schema. With regards, Peter Where do you perform the query in the C++ code? Your C++ program shows how you prepare the statement 5000 times, but not how you execute it. The VB.Net code prepares the statement once, and then executes it 5000 times. It looks like you are testing completely different things. Bert ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search query alternatives.
On Thu, 16 Oct 2014 09:05:51 +1100 Michael Falconer michael.j.falco...@gmail.com wrote: we just wonder if there is a better way to perform this search in SQL. Is there a general technique which is superior either in speed, efficiency or load bearing contexts? The simple answer is No, because SQL is a specification, not an implementation. Different systems implement it differently and therefore perform differently. Any general technique affecting performance belongs to the implementation per se, not the SQL, which is a logical construction. SQLite itself has changed its performance characteristics over the course of its development. For that reason, any question of performance has to be answered in terms of a particular implementation, even its specific version, and the OS and hardware it's running on. That said, there is reason to suppose that a single-table design would be more efficient. If the queries can be expressed with recursion and the indexes lead to efficient searches, the query optimizer has less work to do. It has fewer permutations to consider, and the search is apt to touch fewer pages. The analysis tools of the system you're using should be able to confirm or deny that supposition. I would remind your fellows, though, that efficiency is not all. The utility of a model (that is, the database design) is measured by how well, to its purpose, it describes the real world. Any model that must be changed as that reality changes in predictable ways isn't really much of a model; it turns the designer into a component of the model. By recognizing all trees as one, you generalize your model and make it do work you are now doing yourself (manually, or in application logic). By any measure, that makes it a better model. HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
On 17 Oct 2014, at 10:39am, Prakash Premkumar prakash.p...@gmail.com wrote: So the approach where we open the db with :memory: keyword does not provide durability . Only by increasing the cache size can we make db act as an inmemory db with durablity. That's the conclusion right ? No. The cache size has nothing to do with it. If you explicitly tell SQLite to open a database in memory then it will open a database in memory and not on disk. That is what the documentation says. Can you explain to us why you are asking all these questions comparing SQLite with other things ? You don't seem to know the subjects will enough to ask useful questions and the answers don't seem to get you any advantage. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Inmemory database in sqlite
On 17 Oct 2014 at 10:39, Prakash Premkumar prakash.p...@gmail.com wrote: So the approach where we open the db with :memory: keyword does not provide durability. Why is that any sort of surprise? What you're opening is a database called :memory:. The string :memory: is the name of the database, not a keyword. -- Cheers -- Tim ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE expression with operators from text functions?
FTS MATCH was a great solution to that particular problem. Thanks again, Dr. Hipp! Back to original question... Is it at all possible to pass the results of a SELECT to a WHERE expression? I have used a SELECT to create a full statement but then I have to copy the result to a query editor and execute it. Tom On Oct 16, 2014, at 12:55 PM, Tom Holden ve3...@gmail.com wrote: I think you are right. The FTS MATCH looks like it should function the way I want. Thanks, Richard! I will now learn how to use FTS... Tom On Thu, Oct 16, 2014 at 12:20 PM, Richard Hipp d...@sqlite.org wrote: On Thu, Oct 16, 2014 at 12:03 PM, Tom Holden ve3...@gmail.com wrote: Coming up with a subject was a struggle and maybe that indicates an impossibility. Searching the archive was equally fruitless. Perhaps what you really want is Full Text Search. http://www.sqlite.org/fts3.html What I am trying to do is to build a SELECT with a compound WHERE using one or more run-time parameters. Sort of like transforming an input phrase such as: string1+string2+string3... INTO WHERE [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE '%string3%' AND... This brute force method works: WHERE [Value] LIKE '%'||$SearchString_ONE||'%' AND [Value] LIKE '%'||$SearchString_TWO||'%' ... but requires every parameter to be acted on (filled in or made blank). I can build a statement that produces a desirable looking expression but cannot evaluate it as such with WHERE: SELECT '[Value] LIKE '||'''%'||REPLACE($SearchString, '+', '%'''||' AND [Value] LIKE '||'''%')||'%''' produces [Value] LIKE '%string1%' AND [Value] LIKE '%string2%' AND [Value] LIKE '%string3%' but WHERE (above SELECT...) evaluates to FALSE I need a way to convert the text result to an expression that WHERE evaluates as an expression. Any possibility to do this within SQLite? Tom ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE expression with operators from text functions?
On 10/16/2014 12:03 PM, Tom Holden wrote: I need a way to convert the text result to an expression that WHERE evaluates as an expression. Any possibility to do this within SQLite? with recursive split(str, tail) as ( select null, 'string1+string2+string3' union all select substr(tail, 1, instr(tail || '+', '+')-1), substr(tail, instr(tail || '+', '+') + 1) from split where tail != '' ) select * from mytable where not exists ( select str from split where str is not null and value not like '%' || str || '%' ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] INSERT OR REPLACE in a virtual table implementation....
I have a virtual table implementation, and I would like to use the INSERT OR
REPLACE syntax to simplify actions for the user. In my xUpdate method, for
the case where insertion is occuring,
else if ( argc 1 SQLITE_NULL == sqlite3_value_type ( argv[0] ) ) {
I do check a uniqueness constraint, and return an error
SQLITE_CONSTRAINT_UNIQUE, which I presume is the right thing to do.
So, my question is, is this the right thing to do to get INSERT OR REPLACE
on a virtual table, or something else, or maybe that syntax is not supported
on virtual tables?
Thanks,
-dave
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Thoughts and expertise help
Greetings! I have a program that takes bilingual files and looks for the source strings and reports on the multiples target translations of that source string. For example: Hello, Hola Hello, Hola Hello, Hola! Hello, Dímelo! Hello, Y entoces! Hello, y que! Good Bye, Hasta luego Good Bye, Hasta pronto Good Bye, Hasta Pronto Good Bye, Adios Good Bye, Ciao GoodBye, Adiosito etc. This program is running fine on memory and it is capable of running huge jobs. But, the department is now making the huge jobs huger. Because I am using the local computer program's memory to keep track of these and report at the end, I am constantly running out of memory, as you may have already figured out. This program keeps tracks of: -- all the targets found for once source -- all the files where that target was found -- amount of times target was found -- amount of times that target was found in a file -- targets that = source -- targets left blank I have been a huge fan of SQLite and have implemented it in many functions of our department. (Thanks, Dr. Hipp!). I want to take this program and instead of using the memory, I would like to use SQLite and get rid of the out of memory problem. This report will only be needed once, so the SQLite DB created will be deleted right after the report has been created. And a new one will be created on the next report request. So, what I am planning to do is to create two tables: Sources id, sourceText Targets id, sourceID, targetText, filename The reason why I ask is because I have started a few SQLite projects and I have completely destroyed the implementation of the database schema. This time, I am asking for help to make sure that the correct implementation and database structure is put in place. Any thoughts on this idea? Thanks josé ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] INSERT OR REPLACE in a virtual table implementation....
On 10/18/2014 01:07 AM, dave wrote:
I have a virtual table implementation, and I would like to use the INSERT OR
REPLACE syntax to simplify actions for the user. In my xUpdate method, for
the case where insertion is occuring,
else if ( argc 1 SQLITE_NULL == sqlite3_value_type ( argv[0] ) ) {
I do check a uniqueness constraint, and return an error
SQLITE_CONSTRAINT_UNIQUE, which I presume is the right thing to do.
So, my question is, is this the right thing to do to get INSERT OR REPLACE
on a virtual table, or something else, or maybe that syntax is not supported
on virtual tables?
I don't think so. See the final paragraph here:
http://sqlite.org/c3ref/c_vtab_constraint_support.html
Looks like you have to implement the OR REPLACE support in the xUpdate
method.
Dan.
Thanks,
-dave
___
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] INSERT OR REPLACE in a virtual table implementation....
... On 10/18/2014 01:07 AM, dave wrote: I have a virtual table implementation, and I would like to use the INSERT OR REPLACE syntax to simplify actions for the user. In my xUpdate method, for the case where insertion is occuring, ... on a virtual table, or something else, or maybe that syntax is not supported on virtual tables? I don't think so. See the final paragraph here: http://sqlite.org/c3ref/c_vtab_constraint_support.html Looks like you have to implement the OR REPLACE support in the xUpdate method. Dan. ... Thanks! That's news I can use; don't know how I missed that article, but maybe because I was busy coding... -dave ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-announce] SQLite version 3.8.7
No big deal, but on line 885 of shell.c, did you really mean to test if azArg (of type char**) was greater than 0 rather than not equal to 0? It throws a warning on Solaris 9 with the SUNPro compiler. On Friday, October 17, 2014 10:00 AM, D. Richard Hipp d...@hwaci.com wrote: SQLite version 3.8.7 is now available on the SQLite website: http://www.sqlite.org/ http://www.sqlite.org/download.html http://www.sqlite.org/releaselog/3_8_7.html SQLite version 3.8.7 is a regularly scheduled maintenance release. Upgrading from all prior versions is recommended. Most of the changes from the previous release have been micro-optimizations designed to help SQLite run a little faster. Each individual optimization has an unmeasurably small performance impact. But the improvements add up. Measured on a well-defined workload (which the SQLite developers use as a proxy for a typical application workload) using cachegrind on Linux and compiled with gcc 4.8.1 and -Os on x64 linux, the current release does over 20% more work for the same number of CPU cycles compared to the previous release. Cachegrind is not a real CPU, and the workload used for measurement is only a proxy. So your performance may vary. We expect to see about half the measured and reported improvement in real-world applications. 10% is less than 20% but it is still pretty good, we think. This release includes a new set of C-language interfaces that have unsigned 64-bit instead of signed 32-bit length parameters. The new APIs do not provide any new capabilities. But they do make it easier to write applications that are more resistant to integer overflow vulnerabilities. This release also includes a new sorter that is able to use multiple threads to help with large sort operations. (Sort operations are sometimes required to implement ORDER BY and/or GROUP BY clauses and are almost always required for CREATE INDEX.) The multi-threads sorter is turned off by default and must be enabled using the PRAGMA threads SQL command. Note that the multi-threaded sorter provides faster real-time performance for large sorts, but it also uses more CPU cycles and more energy. As always, please report any problems to the sqlite-users@sqlite.org mailing list or directly to me. Thanks -- D. Richard Hipp d...@sqlite.org ___ sqlite-announce mailing list sqlite-annou...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] [sqlite-announce] SQLite version 3.8.7
Thanks for the report. The bug you found is probably harmless on most systems. But it is certainly worth fixing. http://www.sqlite.org/src/info/19fe4a0a475bd94 On Fri, Oct 17, 2014 at 5:11 PM, Peter Aronson pbaron...@att.net wrote: No big deal, but on line 885 of shell.c, did you really mean to test if azArg (of type char**) was greater than 0 rather than not equal to 0? It throws a warning on Solaris 9 with the SUNPro compiler. On Friday, October 17, 2014 10:00 AM, D. Richard Hipp d...@hwaci.com wrote: SQLite version 3.8.7 is now available on the SQLite website: http://www.sqlite.org/ http://www.sqlite.org/download.html http://www.sqlite.org/releaselog/3_8_7.html SQLite version 3.8.7 is a regularly scheduled maintenance release. Upgrading from all prior versions is recommended. Most of the changes from the previous release have been micro-optimizations designed to help SQLite run a little faster. Each individual optimization has an unmeasurably small performance impact. But the improvements add up. Measured on a well-defined workload (which the SQLite developers use as a proxy for a typical application workload) using cachegrind on Linux and compiled with gcc 4.8.1 and -Os on x64 linux, the current release does over 20% more work for the same number of CPU cycles compared to the previous release. Cachegrind is not a real CPU, and the workload used for measurement is only a proxy. So your performance may vary. We expect to see about half the measured and reported improvement in real-world applications. 10% is less than 20% but it is still pretty good, we think. This release includes a new set of C-language interfaces that have unsigned 64-bit instead of signed 32-bit length parameters. The new APIs do not provide any new capabilities. But they do make it easier to write applications that are more resistant to integer overflow vulnerabilities. This release also includes a new sorter that is able to use multiple threads to help with large sort operations. (Sort operations are sometimes required to implement ORDER BY and/or GROUP BY clauses and are almost always required for CREATE INDEX.) The multi-threads sorter is turned off by default and must be enabled using the PRAGMA threads SQL command. Note that the multi-threaded sorter provides faster real-time performance for large sorts, but it also uses more CPU cycles and more energy. As always, please report any problems to the sqlite-users@sqlite.org mailing list or directly to me. Thanks -- D. Richard Hipp d...@sqlite.org ___ sqlite-announce mailing list sqlite-annou...@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-announce ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- 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] group_concat query performance
-Original Message- From: sqlite-users-boun...@sqlite.org [mailto:sqlite-users- boun...@sqlite.org] On Behalf Of Kraijenbrink - FixHet - Systeembeheer Sent: vrijdag 17 oktober 2014 16:46 To: General Discussion of SQLite Database Subject: Re: [sqlite] group_concat query performance Thanks Bert, You are right. Now the C++ example runs equaly fast. Looks like I have to redesign the Db schema. Have you tested using a transaction over your combined queries? Even for simple read operations this may give a huge performance difference. In our use case (where we use SQLite as Subversion working copy database) the performance problems are usually in the total number of transactions for an operation, not in the queries itself. Bert With regards, Peter Where do you perform the query in the C++ code? Your C++ program shows how you prepare the statement 5000 times, but not how you execute it. The VB.Net code prepares the statement once, and then executes it 5000 times. It looks like you are testing completely different things. Bert ___ 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] Search query alternatives.
I am glad I posted the question. Yes James, there is little I can disagree with in your excellent summary. Even the critique of my perhaps poorly framed question is indeed valid. I take your point regarding spec vs implementation, and in my experience across different rdbms's I have frequently seen evidence which supports your assertions regarding db/os system influence on implementation approaches. Application code too has it's part to play. How do we plan to access the data? There's a strong case too, IMHO, to have flexibility in the design, perhaps leading to differing approaches with the variable types of data we may be storing. The original design decisions were made by someone who was, and still is, essentially a hobby programmer. I don't think he'd ever heard of of Chris Date or Mr Codd at that time and like all novice application programmers, he had little understanding about the effect db design could have on his application and it's source code. His design choices were initially made on the basis of what he could easily understand and what was (as it appeared to him then) easy to program with. It is some time ago, and we who have lived with rdbms's for years get to say, 'that is a horrible design!'. I think R. Smith hit on a point above, regarding code overhead. Yep, plenty of that. And so the lesson is learned the hard way for someone who until recently had viewed normalisation as a way to make coding harder and to slow down the execution of queries. So with that perspective you can perhaps come some way to understanding the why component. On analysis, I agree with suggested design changes at the higher level. i.e. Dynamic tables are at the root of issues going forward. They are requiring tedious application code gymnastics, more difficult query analysis and poorer query performance. I suppose the upside is that it will be a challenge to see what improvements can be made, and that is always fun and games. I kind of like Mr Smith's other suggestion about an SQLITE testbed or prototype. So easy to work with SQLITE, and probably perfect for this task. Thanks all for your contributions. Just FYI James, the application is coded in php and connects to a mysql database. It can be installed either as a browser based, stand alone or client server app. It's common implementation is on low end shared hosts, even free hosting services. So this limits us somewhat to what is commonly allowed on such platforms. Things like Stored Procedures are unfortunately outside our scope when it come to design considerations. Thanks all. On 18 October 2014 02:24, James K. Lowden jklow...@schemamania.org wrote: On Thu, 16 Oct 2014 09:05:51 +1100 Michael Falconer michael.j.falco...@gmail.com wrote: we just wonder if there is a better way to perform this search in SQL. Is there a general technique which is superior either in speed, efficiency or load bearing contexts? The simple answer is No, because SQL is a specification, not an implementation. Different systems implement it differently and therefore perform differently. Any general technique affecting performance belongs to the implementation per se, not the SQL, which is a logical construction. SQLite itself has changed its performance characteristics over the course of its development. For that reason, any question of performance has to be answered in terms of a particular implementation, even its specific version, and the OS and hardware it's running on. That said, there is reason to suppose that a single-table design would be more efficient. If the queries can be expressed with recursion and the indexes lead to efficient searches, the query optimizer has less work to do. It has fewer permutations to consider, and the search is apt to touch fewer pages. The analysis tools of the system you're using should be able to confirm or deny that supposition. I would remind your fellows, though, that efficiency is not all. The utility of a model (that is, the database design) is measured by how well, to its purpose, it describes the real world. Any model that must be changed as that reality changes in predictable ways isn't really much of a model; it turns the designer into a component of the model. By recognizing all trees as one, you generalize your model and make it do work you are now doing yourself (manually, or in application logic). By any measure, that makes it a better model. HTH. --jkl ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Regards, Michael.j.Falconer. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] WHERE expression with operators from text functions?
Igor, that is a most oblique and intriguing approach. I will try it out and try to get my head around it in the next day or so. Thanks, Tom Igor Tandetnik-2 wrote On 10/16/2014 12:03 PM, Tom Holden wrote: I need a way to convert the text result to an expression that WHERE evaluates as an expression. Any possibility to do this within SQLite? with recursive split(str, tail) as ( select null, 'string1+string2+string3' union all select substr(tail, 1, instr(tail || '+', '+')-1), substr(tail, instr(tail || '+', '+') + 1) from split where tail != '' ) select * from mytable where not exists ( select str from split where str is not null and value not like '%' || str || '%' ); -- Igor Tandetnik ___ sqlite-users mailing list sqlite-users@ http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- View this message in context: http://sqlite.1065341.n5.nabble.com/WHERE-expression-with-operators-from-text-functions-tp78653p78697.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
