Re: [sqlite] Deterministic random sampling via SELECT
In the very old days before computers were common, a random number table appeared at the back of many statistical texts. This was used to select a series of random numbers which would then be used as look-up indices into some other data set. You could do the same: 1. generate a list of pseudo-random numbers, using a pre-defined seed value, over the range 1 .. count(*) of records in table, 2. use that list as record id values to select the desired subset of the data in the table. This would be done in two separate operations, possibly with a storage of the generated numbers in a separate table which could be used in the query of the main data. Since it is a pseudo-random number series, you can repeat it as often as needed using the same seed value. Chris On Thu, 7 Nov 2019, at 15:15, Merijn Verstraaten wrote: > > > On 7 Nov 2019, at 19:16, David Raymond wrote: > > > > Along those lines SQLite includes the reverse_unordered_selects pragma > > https://www.sqlite.org/pragma.html#pragma_reverse_unordered_selects > > which will flip the order it sends rows in queries that don't explicitly > > specify an ordering. It's there to assist you in finding spots in your code > > where you might be relying on implicit ordering when you really shouldn't > > be. > > Like the rest of this threads, this is just pointing out why the things > in my initial email don't work, but I already knew that. Which is why I > asked for help to see if there is a way to do what I want that *does* > work. I don't care particularly about the details of "can I control the > order the condition is evaluated", it's just that all reasonable ways > to sample large streams that I know would require a deterministic order. > > If someone has a different/better idea on how to return just a random > sample from a query in a repeatable way, I'm all ears. > > So far the only suggestion was "use some non-deterministic random > sampling method and store the result", but since my samples are large > and I have lots of them, this would balloon my storage by >100x and I > don't have the available storage to make that work. > > - Merijn > > ___ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > Attachments: > * signature.asc ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Unable to create index on attached database
Thank you - the only combination that I did not try works :( On Mon, 12 Jun 2017 08:17:01 + Hick Gunter <h...@scigames.at> wrote: > Try > > Create index t2.idx on link (...) > > Which is what the syntax diagram would recommend. If you ask SQLite > to create an index in t2, it will figure out that the table needs to > be in t2 too. > > -Ursprüngliche Nachricht- > Von: sqlite-users > [mailto:sqlite-users-boun...@mailinglists.sqlite.org] Im Auftrag von > Chris Peachment Gesendet: Sonntag, 11. Juni 2017 17:22 An: SQLite > mailing list <sqlite-users@mailinglists.sqlite.org> Betreff: [sqlite] > Unable to create index on attached database > > Hello All: > > Is this a bug or am I doing something wrong? > > Note: neither test.db nor t2.db exist prior to this test. > > $ sqlite3 test.db > SQLite version 3.19.2 2017-05-25 16:50:27 Enter ".help" for usage > hints. > sqlite> create table account (id integer primary key, idParent > sqlite> integer); attach database 't2.db' as t2; create table t2.link > sqlite> (idParent int, idChild int); create index t2.idx on t2.link > sqlite> (idChild,idParent); > Error: near ".": syntax error > sqlite> create index idx on t2.link (idChild,idParent); > Error: near ".": syntax error > sqlite> create index idx on link (idChild,idParent); > Error: no such table: main.link > sqlite> > > Regards, > > Chris Peachment > > > > > ___ > 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 > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@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@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] Unable to create index on attached database
Hello All: Is this a bug or am I doing something wrong? Note: neither test.db nor t2.db exist prior to this test. $ sqlite3 test.db SQLite version 3.19.2 2017-05-25 16:50:27 Enter ".help" for usage hints. sqlite> create table account (id integer primary key, idParent integer); sqlite> attach database 't2.db' as t2; sqlite> create table t2.link (idParent int, idChild int); sqlite> create index t2.idx on t2.link (idChild,idParent); Error: near ".": syntax error sqlite> create index idx on t2.link (idChild,idParent); Error: near ".": syntax error sqlite> create index idx on link (idChild,idParent); Error: no such table: main.link sqlite> Regards, Chris Peachment ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] links in markdown syntax - local repo vs remote repo (edited)
Hello: I'm resending a slightly modified version of this email because my sample remote url listed below did not include the repository name: http://www/host.com/site/library.cgi/doc/tip/overview.mkd should have been: http://www/host.com/site/library.cgi/repo-name/doc/tip/overview.mkd --- begin original message with edit --- I have started using the markdown feature for embedded documentation files. I'm using Fossil from trunk, version [9d657c3be5] 2013-01-15, compiled myself on the two machines of interest to me. My local machine is running Debian 7 on x86 32 bit and the remote server is running a modified version of CentOs 5 on x86 64 bit. The compile instructions on both machines are: ./configure --markdown make The Admin-Settings-markdown flag is set ON for both the local and remote repositories. The local repository is accessed using the command line instruction: fossil ui The remote repository is accessed via the browser with a url of the form: http://www.host.com/site/library.cgi/repo-name where library.cgi uses the documented script method of invoking Fossil: #!/path-to/fossil directory: /path-to-repo/fossils notfound: http://url-to-go-to-if-repo-not-found/ The 'site' sub-directory in the remote url should not impact on the problem here. The repository home page consists of a few lines of text and a number of links to other embedded documentation pages. I have two versions of the home page: 1. index.wiki with links of the form [/doc/tip/overview.mkd | Overview] 2. index.mkd with links of the form [Overview](/doc/tip/overview.mkd) By changing the Admin-Configuration-Index Page string, I can choose the active version of the home page. The wiki version of the home page operates correctly with the links transformed to include the correct url on both local and remote machines. The markdown version operates correctly on the local machine where the links use the pattern: http://localhost:8080/doc/tip/overview.mkd However, markdown version on the remote machine has an incomplete link: http://www/host.com/doc/tip/overview.mkd when it should be: http://www/host.com/site/library.cgi/repo-name/doc/tip/overview.mkd As a result, the desired page is not found when the link is clicked. If I enter the correct url into the browser address bar then the page is displayed as expected. Since the embedded documentation pages are written on my local machine and then committed to the repository, with subsequent sync to the remote machine, the identical markdown link urls should operate correctly in both situations. It does not make sense to include an absolute path url for the remote repository access. Is there any configuration change I can make to overcome this problem, or am I detecting a weakness in the markdown interface for Fossil? Thanks, Chris Peachment ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] links in markdown syntax - local repo vs remote repo
Hello: I have started using the markdown feature for embedded documentation files. I'm using Fossil from trunk, version [9d657c3be5] 2013-01-15, compiled myself on the two machines of interest to me. My local machine is running Debian 7 on x86 32 bit and the remote server is running a modified version of CentOs 5 on x86 64 bit. The compile instructions on both machines are: ./configure --markdown make The Admin-Settings-markdown flag is set ON for both the local and remote repositories. The local repository is accessed using the command line instruction: fossil ui The remote repository is accessed via the browser with a url of the form: http://www.host.com/site/library.cgi/repo-name where library.cgi uses the documented script method of invoking Fossil: #!/path-to/fossil directory: /path-to-repo/fossils notfound: http://url-to-go-to-if-repo-not-found/ The 'site' sub-directory in the remote url should not impact on the problem here. The repository home page consists of a few lines of text and a number of links to other embedded documentation pages. I have two versions of the home page: 1. index.wiki with links of the form [/doc/tip/overview.mkd | Overview] 2. index.mkd with links of the form [Overview](/doc/tip/overview.mkd) By changing the Admin-Configuration-Index Page string, I can choose the active version of the home page. The wiki version of the home page operates correctly with the links transformed to include the correct url on both local and remote machines. The markdown version operates correctly on the local machine where the links use the pattern: http://localhost:8080/doc/tip/overview.mkd However, markdown version on the remote machine has an incomplete link: http://www/host.com/doc/tip/overview.mkd when it should be: http://www/host.com/site/library.cgi/doc/tip/overview.mkd As a result, the desired page is not found when the link is clicked. If I enter the correct url into the browser address bar then the page is displayed as expected. Since the embedded documentation pages are written on my local machine and then committed to the repository, with subsequent sync to the remote machine, the identical markdown link urls should operate correctly in both situations. It does not make sense to include an absolute path url for the remote repository access. Is there any configuration change I can make to overcome this problem, or am I detecting a weakness in the markdown interface for Fossil? Thanks, Chris Peachment ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite versus MySQL automatic defragmentation on SSDs?
In my scenario there is just one master and most transactions are non-overlapping, but I can foresee a need for resolution of the occasional collision. You mention "other states". Can you explain further? Chris On Wed, 2012-10-31 at 11:40 +0700, David Barrett wrote: > Ah, to clarify, there is only one "master" at any point in time. So this > isn't a "multi-master" scenario where each node keeps committing locally > and then somehow merging the results later. Rather, each node knows if > it's the master or slave (or a variety of other states). If it's a master, > it organizes the two-phase distributed commit. If it's a slave, it > escalates to the master. And if it's something else, then it just holds on > to the request and waits until it's either a slave or a master. > > -david > > > On Wed, Oct 31, 2012 at 2:09 AM, Chris Peachment <ch...@ononbb.com> wrote: > > > On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote: > > > Thanks Alek! Yes, we're definitely planning on it, just trying to > > > find the right time. We don't want to go through the work to open > > > source it only to be greeted with silence. Might you be interested in > > > using it in an actual deployed environment, or just studying it? > > > > > > > > Your proposal to open source the replication method used by Expensify > > has me interested. My application of interest is much smaller than > > yours, just a handful of remote clients that risk loss of connectivity > > but wish to continue with database updates during the downtime. > > > > Aside from the details of protocol usage and statement packaging, the > > concern for collisions during merge is a particular issue of interest. > > > > Chris > > > > > > ___ > > 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] sqlite versus MySQL automatic defragmentation on SSDs?
On Wed, 2012-10-31 at 00:49 +0700, David Barrett wrote: > Thanks Alek! Yes, we're definitely planning on it, just trying to > find the right time. We don't want to go through the work to open > source it only to be greeted with silence. Might you be interested in > using it in an actual deployed environment, or just studying it? > > Your proposal to open source the replication method used by Expensify has me interested. My application of interest is much smaller than yours, just a handful of remote clients that risk loss of connectivity but wish to continue with database updates during the downtime. Aside from the details of protocol usage and statement packaging, the concern for collisions during merge is a particular issue of interest. Chris ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Question limit use for me of sqlite, I need help, please, Marcelo Paiva, Brasil
Here are the steps you must take: 1. Understand that sqlite3 does not have a DATE type, only text. It does have functions that can work with text strings to be treated as dates, for example strftime(). 2. Convert all your dates in the database and in your programmes to use a text format with parts in descending order. This is ISO 8601 (http://pt.wikipedia.org/wiki/ISO_8601) year - month - day (ano - mês - dia) 3. Use 4 digit years like 2012 and not 12. 4. Now it is possible to compare dates in the way you want to do. On Tue, 2012-05-22 at 15:25 -0300, Marcelo Paiva wrote: > Marcelo Paiva, home user, I am bazilian, I don´t speak or write English, I > need help around sqlite?, my question: > > How question in sqlite the sentence like the sentence in Posthe tgreSQL: > > data -> date type -> example -> 22/05/2012 -> diferent -> "22/05/2012" > > question/sentence: select *from tcontsif01 where data>='01/01/2012' and > data<='01/05/2012' > > in sqlite ?? not type data, only TEXT -> in sqlite 22/05/2012 > like,iqual "22/05/2012" or '22/05/2012', the filter negative, order by > negative, > > negative -> select *from tcontsif01 where data>='01/01/2012' and > data<='01/05/2012' > > order by data -> negative -> order by ASCII in sqlite > > please, one light, please solution, please example, please help me > > I understand datetime, understand strtime please help me, please a example > routine sql for sqlite3 with functions, > > Marcelo Paiva > Stante Santa Catarina - Brasil - here is not tropical here is subtropical > here is cold, but here is Brasil ... please help me > ___ > 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] I'm sure I'm missing something.
The select statement needs an 's' on 'motorcycle' to match the table definition. Or you can remove the 's' in the create statement. On Sun, 2012-01-29 at 20:03 -0400, Chris Peachment wrote: > SqlText = "select description from motorcycle " > " where model like :model"; > ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] I'm sure I'm missing something.
I don't usually do other people's homework but
it's Sunday night so why not be generous?
At the terminal command line type:
sqlite3 motorcycles.db
At the resulting sqlite3 command line type:
create table motorcycles (
model text;
description text
);
insert into motorcycles
values ('FLSTCI', 'That is a Heritage Softail Classic');
insert into motorcycles
values ('WXYZ', 'That is NOT a Heritage Softail Classic');
.quit
In your text editor create a C main function
that will call a function like this one after
getting input from user.
#include
#include "sqlite3.h"
int
getMotorCycleDescription (char *Model,
char *Description)
{
int Result = 0;
char *DbFileName;
char *SqlText;
sqlite3*hDB;
sqlite3_stmt *hStmt;
DbFileName = "motorcycles.db";
Result = sqlite3_open_v2(DbFileName, ,
SQLITE_OPEN_READWRITE, NULL);
// Configure database activity features
// relevant to bigger applications.
if (Result == SQLITE_OK) {
// force referential integrity
Result = sqlite3_exec(hDB, "PRAGMA foreign_keys = 1",
NULL, NULL, NULL);
// allow delays for concurrent access
sqlite3_busy_timeout(hDB, 500);
}
if (Result == SQLITE_OK) {
// Use prepare, bind and step rather than exec
// to avoid sql injection attacks.
SqlText = "select description from motorcycle "
" where model like :model";
Result = sqlite3_prepare_v2(hDB, SqlText, strlen(SqlText),
, NULL);
}
if (Result == SQLITE_OK) {
Result = sqlite3_bind_text(hStmt, 1, Model, -1, SQLITE_STATIC);
if (Result == SQLITE_OK) {
Result = sqlite3_step(hStmt);
if (Result == SQLITE_ROW) {
strcpy(Description, (char *) sqlite3_column_text(hStmt, 0));
Result = SQLITE_OK;
}
}
sqlite3_finalize(hStmt);
}
sqlite3_close(hDB);
return Result;
} // getMotorCycleDescription
Add suitable display routine.
Compile, link and test.
Ypu might want to use the strlcpy and strlcat functions
in place of strcpy and strcat to reduce the risk of buffer
overflow errors.
On Sun, 2012-01-29 at 14:26 -0800, Bruce Steele wrote:
> I’m a very new “C” programmer working on a program to look up information
> from a SQLite created data base.
> The data base is made up of information on models of Harley Davidson
> motorcycles. I want to do a search of the data base using input from the
> “user”. For instance the user wants to know what model of motorcycle is a
> “FLSTCI”. They would type that in and my “C” program will then access the
> data base and return “That is a Heritage Softail Classic”.
>
> My problem is can’t see a way to pass the user input to my select statement.
> Can this be done?
> Is it SQL or C or a combination?
> Or I going about this all wrong?
>
> Like I said I’m very new and just trying to learn. Thanks for your help. Just
> point me in the right direction and I’ll figure it out, Thanks again.
> ___
> 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] Propose minor incompatible API change
On Mon, 2011-01-10 at 19:54 -0500, Richard Hipp wrote: > This is, technically, a compatibility break. On the other hand, there > appear to be vast numbers of smartphone applications that currently depend > on undefined behavior and will suddenly stop working if we don't make this > change. > What's wrong with using a new function: sqlite3_step_v2() ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Schema design and/or SELECT construction
What's wrong with a properly normalised schema like this: create table main (id, name,...); create table keyword (id, label); create table crossref (id_main, id_keyword); The concept of core and other keywords is a bit arbitrary. What is important (i.e. core) today might not be so tomorrow. Parsing comma separated lists in a single attribute is likely to be a bother. On Mon, 2009-06-01 at 21:21 -0700, Craig Smith wrote: > Hello: > > I have about 3000 electronic images that I am preparing to distribute > to my family members. They use a variety of operating systems, so I > am providing the images on remote hard drives, with the images divided > into folders based on years the images were created. All images were > obtained via scanning of negatives and slides, and each image has been > keyworded in EXIF format. > > What I want is to create a master SQLite database catalog of all the > images, and I have come up with a proposed schema: > > TABLE main (all pertinent image data such as date, location in folder, > etc.) > TABLE core_keywords (id, name) --This table would only hold the names > of my immediate family members, each with a corresponding id > TABLE other_keyword (id,name) -- all other keywords > > I have thought to create two keyword fields in the main table, one to > hold the ids of the core_keywords (comma separated) and one to hold > the ids of the other_keywords, also comma separated. What I cannot > devise is an elegant method to SELECT based on the core_keywords to > achieve the following sorts: > > 1- Find images with a single core_keyword id, that is, only images of > a single person, no other core persons in the image > 2- Find images with a specific set of core_keyword ids, such as 1 and > 6 or 2 and 5 and 7, etc., with no other core persons in the image > > The idea is to create a document with lists of all images that are > exclusive to single individuals, specific pairs, etc., so that family > members can easily find themselves or groups, regardless of image > catalog software they use on their particular systems, which may or > may not be able to perform these types of sorts. > > I am not asking anyone to actually write the SELECT statements for me, > but rather point me toward the operands that would achieve my goal. I > have read through the documentation, and I cannot seem to generate the > logic in my head to SELECT WHERE core_id is only 4. > > If anyone has an idea on a more efficient database design, or TABLE > schema, please do not hesitate to proffer your thoughts. I am hoping > to have it all figured out BEFORE I load up the tables with data. (I > am actually still scanning images at this stage, but trying to prepare > for the next phase.) > > Thank you very much for your time and consideration. > > > Craig Smith > cr...@macscripter.net > > > > ___ > 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] rowid increment
Use an autoincrement field in the table instead. On Wed, 2008-10-29 at 09:46 -0700, Shaun R. wrote: > The rowid looks to reuse numbers when a row is deleted, how can i stop this? > > For example, i insert 3 rows, then i delete row 2, when entering a new row > after that the rowid is 2 rather than 4. I want the rowid to be 4. > > ~Shaun > > > ___ > 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] Trying to use SQLite3 with PHP5....
Ubuntu version 7.10 has available PHP version 5.2.3-1ubuntu6.2 that includes Sqlite3 version 3.5.2 accessible with both PDO and sqlite libraries. Personally, I prefer to use PDO because a single line change in the PHP code when declaring the database name to the PDO new() function permits use with both Sqlite3 and MySql. I don't know what versions are available with other Linux distributions. On Fri, 2008-01-04 at 11:09 -0500, Philip Butler wrote: > Hi all, > > I am revisiting this topic again - I have tried a LONG time ago and > gave up > > I am wanting to use SQLite3 with PHP (5.2.5).. I have 2 issues... > > 1) > I can't seem to compile sqlite3 on a Linux system. It wants to use > TCL which I don't have installed. I have used: > > configure --disable-tcl > > and it still wants some tcl utility to (from what I think) build the > sqlite3.c file. I have searched the email archives and have seen > others with the same problem - but haven't found an answer that works > for me. I have punted trying to compile the source and have installed > the precompiled sqlite3 files - sqlite-3.5.4.so, sqlite3-3.5.4.bin, > sqlite3_analyzer-3.5.4.bin. > > > > > 2) > Now comes the fun part - I can't figure out how to get sqlite3 to play > right with PHP. From what I can tell, PDO is needed to do this - but > from the docs, it seems like I need to be able to compile sqlite3 > (i.e. issue #1 above). So it seems that I am stuck. Does anyone have > any notes they can share on getting the latest sqlite to work with the > latest PHP ?? Ideally, the PHP team would incorporate sqlite3 into > the PHP sources (like they do with sqlite2), but this hasn't been done. > > I have been down this path before (a year or two ago) - posted to > forums/maillists on both SQLite and PHP. The PHP people say it's a > sqlite issue and the sqlite people said it was a PHP issue. > Therefore, I have been using Postgresql for the past few years. I > don't have a problem with Postgres - but want to try something "lean > and mean". > > > Thanks, > > Phil Butler > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [Linux + PHP] Recommended way to access SQLite?
According to the php info() function, on Ubuntu, PHP Version
5.2.3-1ubuntu6.2 was released on 3 December 2007 and includes the
PDO sqlite driver for sqlite version 3.5.2.
It also includes the extension library sqlite driver for version
2.8.17 so you have your choice there.
Personally, I use PDO with sqlite on my local server as a direct
substitute for PDO with mysql on the remote server. If you use only
simple sql statements then the only change needed is something like:
define("SERVER_MYSQL", 1);
define("SERVER_SQLITE", 2);
define("DATABASE_SERVER", SERVER_SQLITE);
// define("DATABASE_SERVER", SERVER_MYSQL);
try {
if (DATABASE_SERVER == SERVER_MYSQL) {
$dbh = new PDO("mysql:host=localhost;dbname=tasks", "tasks",
"tasks");
}
else {
$dbh = new PDO('sqlite:tasks.db');
}
}
On Mon, 2007-12-10 at 02:07 +0100, Kees Nuyt wrote:
> On Sun, 9 Dec 2007 23:34:44 +0100, DJ Anubis
> <[EMAIL PROTECTED]> wrote:
>
> >Le dimanche 9 décembre 2007, Gilles Ganault a écrit :
> >> It seems like I have two options:
> >> - calling the SQLite library
> >> - going through the PDO interface, and its SQLite module.
> >>
> >> Which of the two would you recomend? Are there other options I
> >> should know about?
> >
> >I would recommend using PDO interface, as this is the standard
> >PHP5 API.
>
> I agree. My experience with php_pdo_sqlite is positive, and I
> think it is the easiest way to use sqlite3 in PHP.
>
> I didn't try php_pdo_sqlite_external yet, it seems to call a
> self-supplied sqlite3.dll, so one would be able to use the
> latest SQLite3 version.
>
> >Don't worry about PHP4, as this old version will no more be
> >supported soon...
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] What is the preferred way to create SQLite databases?
At the command line prompt: sqlite3 name_of_database_to_be_created SQLite version 3.5.2 Enter ".help" for instructions sqlite>.read database_definition_text_file_name sqlite>.quit On Thu, 2007-11-29 at 15:43 -0600, Mark Brown wrote: > Hi- > > What is the preferred way to create SQLite databases? Ideally, I would like > to have the entire schema in a file, and then generate the DB from that, as > we make changes to the schema often. > > We're currently using something called SQLite Database Browser, but I am > becoming concerned that the version of SQLite it is using is not the same as > the version of SQLite we are using in our application. > > My developers all have Windows machines, but our application that uses > SQLite runs on vxWorks. > > Thanks, > Mark > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] quickstart
Here is a small code sample.
Open the database and then:
Result = sqlite3_prepare_v2(hdb,
"select Name, FilePrefix from Application", 100, , 0);
if (Result != SQLITE_OK) {
fprintf(stderr,
"cannot select from Application table. Error Code: %d\n", Result);
exit(1);
}
do {
Result = sqlite3_step(hstmt);
if (Result == SQLITE_ROW) {
... handle row data using the sqlite3_column_xxx functions
}
} while (Result == SQLITE_ROW);
if (Result == SQLITE_DONE) {
Result = SQLITE_OK;
}
else {
fprintf(stderr,"Error Code: %d\n", Result);
}
sqlite3_finalize(hstmt);
sqlite3_close(hdb);
On Thu, 2007-11-29 at 14:03 -0500, Wilson, Ron wrote:
> So a friend of mine was asking me how to get started using SQlite, so I
> pointed him here:
>
> http://www.sqlite.org/quickstart.html
>
> This page still shows the old callback method for usign sqlite3_exec().
> I actually haven't used SQlite in quite a long time, but judging from
> recent list topics, this is no longer the preferred method. So where do
> I point my friend for using sqlite3_prepare_v2() etc.? I've seen a few
> posts (long since deleted from my inbox) that had basic outlines. Could
> someone please post a basic code snipit for open/query/close using the
> newer interface, i.e. avoiding the callback usage? Perhaps the
> quickstart guide (above) could use an update as well?
>
> RW
>
> Ron Wilson, Senior Engineer, MPR Associates, 518.831.7546
>
> -
> To unsubscribe, send email to [EMAIL PROTECTED]
> -
>
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Spatial searches
On Thu, 23 Aug 2007 10:03:00 -0700, David Thieme wrote: >I've been looking for a WinCE embedded database that supports spatial >searches. We are already using SQLite for a very small application; we're >hoping that someone may have some tricks/hints on how to implement fast >searches on spatial data with SQLite. A typical search would be finding >items whose lat/lon falls within a given rectangle (e.g., hotel's closest to >my car). If not, can someone recommend a WinCE database engine that >supports spatial searches? > >David What is wrong with: select * from SpacialData where (SpacialData.PointLatitude <= CurrentLatitude + LatOffset) and (SpacialData.PointLatitude >= CurrentLatitude - LatOffset) and (SpacialData.PointLongitude <= CurrentLongitude + LonOffset) and (SpacialData.PointLongitude >= CurrentLongitude - LonOffset); Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 18:58:32 +0200, Kees Nuyt wrote: >Hi Chris, >On Thu, 23 Aug 2007 12:14:51 -0400, you wrote: >>On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >> >>>Chris Peachment wrote: >>>> I have a database with more than 200,000 records in the >>>> core table. An update table of similar record count contains >>>> a proper subset of the core table columns. >>>> >>>> I'm looking for a fast method of merging the values in the >>>> two tables such that : >>>> >>>> 1. core table columns are updated, and >>>> 2. non-existent core records are inserted from the update table. >>>> >>>Will INSERT OR REPLACE do what you want? >> >> >>>Gerry >> >> >>Regrettably no. When an existing core record is found then it >>is deleted before the insert. That means that all columns are >>given new values and not just the ones to be updated. >That is exactly what INSERT OR REPLACE does. >http://www.sqlite.org/lang_insert.html >http://www.sqlite.org/lang_conflict.html Sorry for the confusion I introduced. I know the behaviour of INSERT OR REPLACE is as-described, and that is NOT what I want. I need to keep the non-updated columns. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Update Columns in One Table Using Values From Another Table
On Thu, 23 Aug 2007 08:52:40 -0700, Gerry Snyder wrote: >Chris Peachment wrote: >> I have a database with more than 200,000 records in the >> core table. An update table of similar record count contains >> a proper subset of the core table columns. >> >> I'm looking for a fast method of merging the values in the >> two tables such that : >> >> 1. core table columns are updated, and >> 2. non-existent core records are inserted from the update table. >> >Will INSERT OR REPLACE do what you want? >Gerry Regrettably no. When an existing core record is found then it is deleted before the insert. That means that all columns are given new values and not just the ones to be updated. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Update Columns in One Table Using Values From Another Table
I have a database with more than 200,000 records in the core table. An update table of similar record count contains a proper subset of the core table columns. I'm looking for a fast method of merging the values in the two tables such that : 1. core table columns are updated, and 2. non-existent core records are inserted from the update table. It is not necessary that records missing from the update table cause deletes in the core table. Case 1 above needs something like: update Core set B = (select UpdateTable.B from UpdateTable as U where U.A = Core.A) set C = (select UpdateTable.C from UpdateTable as U where U.A = Core.A) ... where exists (select U.A from UpdateTable as U where U.A = Core.A); The table schema include indexes on the A columns, but this operations takes more than 10 minutes (and still not finished) when using sqlite3 from the command line. This is too long for use with PHP and a web-browser since the server kills the process after 30 seconds. Can anyone help? Chris Peachment - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Making SQL queries with date conditions?
Why don't you try it and see? Use the sqlite3 command line interpreter with the a sample database. On Sun, 19 Aug 2007 13:28:20 +0200, Daniel Cohen Gindi wrote: >Can I create a table with DATE datatype? Or just use text datatype with >-mm-dd? >I can see that sorting will be easy with just text -mm-dd, >But what about BETWEEN or > >Thanks >Daniel > >-Original Message- >From: Chris Peachment [mailto:[EMAIL PROTECTED] >Sent: Sunday, August 19, 2007 12:19 PM >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Making SQL queries with date conditions? > >Use the ISO date format mmdd or -mm-dd >and your sort and search problems disappear. > >On Sun, 19 Aug 2007 12:16:41 +0200, Daniel Cohen Gindi wrote: > >>Hi guys! > >> > >>Is there any way to make SQL queries, with the WHERE clause containing >>dates? I mean [WHERE col > 05/12/2007] or such? > >>It just that I have noticed that there's no DATE datatype. > >>If there's no way, is it easy to override the text comparing operators (<=, >>>=, <, >) ? > >> > >>Thanks a lot! > > > > > > > >- >To unsubscribe, send email to [EMAIL PROTECTED] > >- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Making SQL queries with date conditions?
Use the ISO date format mmdd or -mm-dd and your sort and search problems disappear. On Sun, 19 Aug 2007 12:16:41 +0200, Daniel Cohen Gindi wrote: >Hi guys! > >Is there any way to make SQL queries, with the WHERE clause containing >dates? I mean [WHERE col > 05/12/2007] or such? >It just that I have noticed that there's no DATE datatype. >If there's no way, is it easy to override the text comparing operators (<=, >>=, <, >) ? > >Thanks a lot! - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
On Thu, 2 Aug 2007 14:16:28 -0700 (PDT), Joe Wilson wrote: >--- John Stanton <[EMAIL PROTECTED]> wrote: >> Sqlite3 will get into a tangle with certain sequences where it does not >> accept a semicolon as a terminator or obey a CTL c. >To reproduce: >1. build sqlite3 without readline support. >2. run sqlite3 in an xterm >3. at the prompt, press cursor up >4. hit return >Nothing you do at this point will work except for Ctrl-\ to kill the process. >SQLite version 3.4.1 >Enter ".help" for instructions >sqlite> ^[[A > ...> .q > ...> ; > ...> select 1; > ...> .q > ...> .h > ...> ; > ...> ; Is this a weakness in the scanner or its state table? Is readline() providing the command history feature that is so valuable during repetitive debugging? And for which the up arrow is an essential keystroke? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] UI question
On Thu, 02 Aug 2007 08:21:30 -0600, Scott Derrick wrote: >This is probably a stupid question but has frustrated me a couple of times. >When using the command line interface sqlite3, a couple of times I have >forgotten to use the "." before a command. After that I get a "...>" >prompt that I can't seem to escape from and accepts no commands? My >only choice is to shut down that terminal and start a new one.. >There must be an easy way to get back to the command mode? And what is >the "...>" mode? >Scott Try typing the semi-colon to terminate the sql statement. You should get a syntax error report and no further action. The command prompt should appear on the next line. Chris - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Re: Order of result of a query?
Why not include another column that specifies the precise display sequence that you need and use ORDER BY on it? On Tue, 15 May 2007 19:44:44 +0530, B V, Phanisekhar wrote: >>> Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? >Actually I was getting the result (1, 2, 5, 6), but I wanted the result >as per the given order in the OR clause "2 or 8 or 7 or 3". I wanted to >know how sqlite works internally. >>> Using which query we can get the result (1, 6, 5, 2)? >Assume the values in the OR clause, be replaced by some subquery. Then >in such scenarios how will I be able to maintain the order? >I want the order of the subquery to be preserved in the main query. >For e.g.: for the sub query returned values in order (2, 8, 7, 3), I >need the main query should to return (1, 6, 5, 2) not (1, 2, 5, 6). >Here (2, 8, 7, 3) is not fixed, which u have assumed in your reply. >Regards, >Phani >-Original Message- >From: Igor Tandetnik [mailto:[EMAIL PROTECTED] >Sent: Tuesday, May 15, 2007 5:27 PM >To: SQLite >Subject: [sqlite] Re: Order of result of a query? >B V, Phanisekhar <[EMAIL PROTECTED]> wrote: >> Assume the database given below >> >> mainTable (rowid INTEGER, puid INTEGER) >> >> Assume main table be >> >> Rowid Puid >> 1 2 >> 2 3 >> 3 4 >> 4 6 >> 5 7 >> 6 8 >> >> "select rowid from mainTable where Puid = 2 OR puid = 8 OR puid = 7 >> OR puid = 3" >> >> Will the result of the above query be (1, 6, 5, 2) or (1, 2, 5, 6)? >Why don't you try it and see for yourself? >Note that, without ORDER BY clause, the order of records is an >implementation detail you should not rely on. It may be (1, 6, 5, 2), or >(1, 2, 5, 6), or something else. If you want a particular order, specify >it explicitly. >> Using which query we can get the result (1, 6, 5, 2)? >select rowid from mainTable where Puid in (2, 8, 7, 3) >order by (case Puid when 2 then 1 when 8 then 2 when 7 then 3 when 3 >then 4 end); >Igor Tandetnik > >- >To unsubscribe, send email to [EMAIL PROTECTED] > >- >- >To unsubscribe, send email to [EMAIL PROTECTED] >- - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Problem with SQLite Pecl extension for PHP4
I think I have misread your original posting.
Can you upgrade your version of PHP?
I use PHP version 5.2.1.
Running phpinfo() reveals the PDO driver to be:
PECL version: 1.0.1 $Id: pdo_sqlite.c,v 1.10.2.6.2.1 2007/01/01 09:36:05
sebastian Exp $
Sqlite Library: 3.3.7undefined
Since you can run sqlite3 against the database without issue,
it appears that the version embedded in PHP is the problem.
Chris Peachment
--
On Thu, 10 May 2007 08:46:30 -0400, Brandon Eley wrote:
>Thank you for this information. How would I access the 2.8.x database
>from within PHP? is it even possible?
>The database is created from a shopping cart program, so I can't
>upgrade it, it has to remain the same version (for now).
>Brandon Eley
>[EMAIL PROTECTED]
>On May 9, 2007, at 10:59 PM, Chris Peachment wrote:
>> There was a change of file structure with version 3.x.x
>> and this is described in the documentation under Version 3
>> Overview.
>>
>> You are attempting to use Sqlite3 on a version 2.8.x database
>> and the formats are not compatible.
>>
>>
>> On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:
>>
>>> I've gone through a few tutorials and can't seem to get this right.
>>> I've had my web host install the PECL extension and the following is
>>> what is in my phpinfo();
>>
>>> sqlite
>>> SQLite support enabled
>>> PECL Module version 1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
>>> 12:25:33 wez Exp $
>>> SQLite Library 2.8.14
>>> SQLite Encoding iso8859
>>
>>> Directive Local Value Master Value
>>> sqlite.assoc_case 0 0
>>
>>> ***
>>
>>> This is the PHP code I'm using:
>>
>>> >> $db = sqlite_open("/home/xxx/data/xxx.db");
>>> ?>
>>
>>> ***
>>
>>> This is the error I'm getting:
>>
>>> Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
>>> is not a database in /home/xxx/public_html/test.php on line 140
>>
>>> ***
>>
>>> What am I doing wrong? I can run queries all day long using the
>>> command line sqlite3 but I can't seem to get PHP to read the stinkin
>>> database!
>>
>>> Any help would be GREATLY appreciated!
>>
>>> Brandon Eley
>>> [EMAIL PROTECTED]
>>
>>
>>
>>
>>
>>
>>
>>
>> --
>> ---
>> To unsubscribe, send email to [EMAIL PROTECTED]
>> --
>> ---
>>
>-
>To unsubscribe, send email to [EMAIL PROTECTED]
>-
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Problem with SQLite Pecl extension for PHP4
There was a change of file structure with version 3.x.x
and this is described in the documentation under Version 3
Overview.
You are attempting to use Sqlite3 on a version 2.8.x database
and the formats are not compatible.
On Wed, 9 May 2007 19:42:06 -0400, Brandon Eley wrote:
>I've gone through a few tutorials and can't seem to get this right.
>I've had my web host install the PECL extension and the following is
>what is in my phpinfo();
>sqlite
>SQLite support enabled
>PECL Module version1.0.3 $Id: sqlite.c,v 1.62.2.25 2004/07/10
>12:25:33 wez Exp $
>SQLite Library 2.8.14
>SQLite Encodingiso8859
>Directive Local Value Master Value
>sqlite.assoc_case 0 0
>***
>This is the PHP code I'm using:
>$db = sqlite_open("/home/xxx/data/xxx.db");
>?>
>***
>This is the error I'm getting:
>Warning: sqlite_open() [function.sqlite-open]: file is encrypted or
>is not a database in /home/xxx/public_html/test.php on line 140
>***
>What am I doing wrong? I can run queries all day long using the
>command line sqlite3 but I can't seem to get PHP to read the stinkin
>database!
>Any help would be GREATLY appreciated!
>Brandon Eley
>[EMAIL PROTECTED]
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
