Re: [sqlite] Sqlite 2
download url like this http://www.sqlite.org/sqlite-2_x_xx.zip exam:http://www.sqlite.org/sqlite-2_8_17.zip -- View this message in context: http://www.nabble.com/Sqlite-2-tp22944102p25199847.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
Re: [sqlite] Viable alternatives to SQL?
On 28 Aug 2009, at 9:56pm, Beau Wilkinson wrote: >> I want a query language that non-techies can use easily, but also >> supports arbitrarily complex queries. Does such a language exist? > > I remember reading once, in an old book about RDBMS, that SQL was > intended to be something that non-technical decision-maker types > could learn in a few hours. Apparently some early proponents of SQL > had a naive vision of Robert McNamara-types writing queries like > "SELECT NAME FROM PRODUCT ORDER BY GROSS_MARGIN" and then basing > decisions on the result. All that was necessary (they thought) was a > little abstraction (i.e. SQL) to hide the accidental difficulties of > "computer language." > > Oh, to be so young and stupid again... In the 1980s there was a rash of Database Query Languages which claimed to be English-like. In fact I remember two particular languages which actually called themselves 'English'. Think of a very early very simple version of SQL, with a few changes so the parser could ignore words like 'the' and 'to'. They could be great to demo, because the person running the demo would be a language-lawyer with an excellent knowledge of what the parser could and couldn't handle. But as soon as you sat a non-techie in front of them it became obvious just how flexible English is. The ones I remember were query languages only: they had the equivalent of 'SELECT' but no 'INSERT' or 'UPDATE'. They also had commands which rendered various types of graph: not all output was in the form of text. The lack of ability to change meant that they could be harmless to the database files since they could be run perfectly well with read- only access. I suspect that most of them were front-ends to already- existing spreadsheet or DBMS systems. And there's nothing to stop someone writing such a front-end themselves: take a sentence in a natural language and convert it to a SQL command. To get back to the original question, I don't know of anything superior to SQL in this aspect. Anything sufficiently flexible will suffer from injection opportunities. Anything that isn't forces the user to learn a formal language. Simon. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Viable alternatives to SQL?
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
I remember reading once, in an old book about RDBMS, that SQL was intended to
be something that non-technical decision-maker types could learn in a few
hours. Apparently some early proponents of SQL had a naive vision of Robert
McNamara-types writing queries like "SELECT NAME FROM PRODUCT ORDER BY
GROSS_MARGIN" and then basing decisions on the result. All that was necessary
(they thought) was a little abstraction (i.e. SQL) to hide the accidental
difficulties of "computer language."
Oh, to be so young and stupid again...
From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] On
Behalf Of Darren Duncan [dar...@darrenduncan.net]
Sent: Thursday, August 27, 2009 2:16 PM
To: General Discussion of SQLite Database
Subject: Re: [sqlite] Viable alternatives to SQL?
Kelly Jones wrote:
> Many sites let you search databases of information, but the search
> queries are very limited.
A fact I can agree with time and again.
> I'm creating a site that'll allow arbitrary SQL queries to my data (I
> realize I'll need to handle injection attacks).
Now by "arbitrary SQL queries", you mean "arbitrary SQL SELECT statements" I
assume; otherwise, no need for injection as you're already letting them do
whatever they want.
Generally speaking, if you truly want arbitrary queries, you essentially have to
provide a facility to users that is analogous to letting them write in a
programming language, like SQL. To a large extent, you could accomplish this
either by providing lots of form fields where they build up queries by picking
from smaller rules, or alternately you could let them type an actual query like
one would with SQL but instead they write in some simpler language, say one of
your own design, which you then parse and translate to SQL for SQLite to run.
To prevent injection attacks, you in the many-fields case make sure to escape or
validate/restrict all inputs to allowed values, or in the latter case you simply
don't provide features in your simpler query language that you don't want them
to have, such as non-read queries.
> Are there other viable ways to query data? I read a little on
> "Business System 12" (BS12), Tutorial D, and even something called
> T-SQL (I think), but they all seem theoretical and not fully
> implemented.
Business System 12 is a legacy project, one of the original relational database
implementations, that predates SQL. It isn't a separate language and has no
bearing on using with SQLite.
T-SQL is a variant of SQL used by the likes of Sybase and MS SQL Server, I
think, and maybe some other DBMSs; T-SQL is for those DBMSs what PL-SQL is for
Oracle. You see T-SQL/PL-SQL in SQL stored procedures, which SQLite doesn't
natively support anyway.
Tutorial D is indeed an actual language which isn't tied to a specific DBMS, as
with generic SQL itself, and could potentially be something SQLite could support
directly in the future, but it doesn't now. There *are* several Tutorial D
implementations, but not over SQLite. A Java DBMS named "Rel" supports it for
one thing, and also the major SQL DBMS named Ingress is looking to add support
for it as a native language.
The syntax of Tutorial D is superficially like SQL and has most of the same
features, but with some extra features and some omission of mis-features.
For example, here are some simple query comparisons (I think):
SQL:
SELECT * FROM mytable
TD:
mytable
SQL:
SELECT col1, col2 FROM mytable
TD:
mytable{col1, col2}
SQL:
SELECT * FROM mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND
col2 = 'quux'
TD:
mytable WHERE col1 = 'foo' AND col2 = 'bar' OR col1 = 'baz' AND col2 = 'quux'
or:
mytable MATCHING RELATION { TUPLE { col1('foo'), col2('bar') }, TUPLE {
col1('baz'), col2('quux') } }
> I want a query language that non-techies can use easily, but also
> supports arbitrarily complex queries. Does such a language exist?
That actually describes SQL to some extent (and Tutorial D). Compared to other
general purpose languages, SQL is fundamentally easier to use, because it
focuses on people just saying "what" they want to happen rather than "how".
If you want your solution now, and use SQLite, you either may have to roll your
own solution, and/or look at the various database wrapper frameworks out there
(there are a bunch for Perl for example) which may help you do this.
I will also say that I'm making a solution for constructing arbitrarily complex
relational or SQL queries out of data structures in Perl, focusing on enabling
what you can do with stored procedures (which includes all other queries), which
would work with SQLite. But it isn't ready to use yet. You might be able to
use it though depending on your time table. This project is multi-pronged, and
see http://mm.darrenduncan.net/pipermail/muldis-d
Re: [sqlite] Problem invoking php functions from a trigger
Thank's Swithun
but I still have trouble.
Ok, I find out how to register functions with PDO_Sqlite extensions. Just in
case someone is in the same situation, the way to do it is this:
$dbh = new PDO('sqlite:/whatever.sqlite');
$dbh->sqliteCreateFunction('Test','Test');
But I still have a problem:
If I use direct the test funcion in a quuery like
$res=$dbh->query("select test() from table");
it works, but if function test is invoked from a triiger it will say, my
sentence will be somethin like
$modulo=$dbh->query("UPDATE test SET x = 1");
I get this:
[0] => HY000
[1] => 1
[2] => no such function: Test
seems like the trigger is not using the same "$dbh"
¿any clue?
Thx
2009/8/28 Swithun Crowe
> Hello
>
> AR if I run this php script
> AR
> AR *$dbh = new PDO('sqlite:/var/www/test.sqlite');
> AR $sql="INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES
> (
> AR '2221' , '2121' , '1212' , '1212' ) ";
> AR $modulo=$dbh->query($sql);
> AR print_r($dbh->errorInfo()); *
> AR
> AR from outside SQLiteManager I get this errorInfo():
> AR
> AR *Array
> AR (
> AR [0] => HY000
> AR [1] => 1
> AR [2] => no such function: test
> AR )*
>
> Being able to execute PHP functions from inside SQL is a bit of a bonus
> feature. I imagine that PDO, which implements a subset of many SQL
> databases' features, doesn't reach this far.
>
> If you use the SQLite3 extension in PHP, you can register your own
> functions. You would create the function in your PHP source, and then
> register it when you open the database, and then call it from your SQL
> statements.
>
> Perhaps you could keep your "user_function" table, and query it to get the
> PHP code for each function, eval it somehow and then register it with the
> database connection.
>
> There is more here:
>
> http://www.php.net/manual/en/sqlite3.createfunction.php
>
> I hope this helps.
>
> Swithun.
> ___
> 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] Will referential constraints be supported in the future ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Anders Moe wrote: > I think Sqlite is great, but I'm surprised referential integrity contraints > have not been implemented a long time ago. Does anyone know if this is > anywhere on the roadmap ? Lookup the genfkey command in the shell. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqX/CIACgkQmOOfHg372QTAHQCglI/97k7yPIbpTH92CEsnEqGw ptoAoIxaGcK1i/NN54J6zIL/7ry6MOW5 =MK+Z -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Yes, you are right (as always). Time with SQLITE_THREADSAFE=0 is about 4.33 seconds now. Thanks a lot. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:23 PM, D. Richard Hipp wrote: > > On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > >> On Fri, 28 Aug 2009, Marco Bambini might have said: >>> >>> Version 3.4.2 takes about 5.06 seconds (average value) while version >>> 3.6.17 takes about 7.28 seconds (average value). >>> Could be a slowdown in the library for the complexity added over the >>> years or does someone have another possible explanation? > > Our measurements show a substantial performance improvement over > 3.4.2. > > Perhaps you are running in the default configuration, which has been > augmented with many new mutexes since version 3.4.2 in order to make > SQLite proof against over-zealous users of threads. If you recompile > with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance > back. > > >>> >>> Thanks. >> >> Did you just relink your app or did you also migrate the data to a >> new >> sqlite3 database? I think the migration command is: >> >> echo '.dump' | sqlite3 $db | sqlite3 $dbnew >> >> Maybe the internal database structure has changed? > > The file-format is unchanged. > > D. Richard Hipp > d...@hwaci.com > > > > ___ > 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] Explanation
On Aug 28, 2009, at 11:17 AM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? Our measurements show a substantial performance improvement over 3.4.2. Perhaps you are running in the default configuration, which has been augmented with many new mutexes since version 3.4.2 in order to make SQLite proof against over-zealous users of threads. If you recompile with -DSQLITE_THREADSAFE=0, perhaps you will get your old performance back. >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? The file-format is unchanged. D. Richard Hipp d...@hwaci.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Explanation
Library is statically linked into the final app and the db is newly created... -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ On Aug 28, 2009, at 5:17 PM, Mike Eggleston wrote: > On Fri, 28 Aug 2009, Marco Bambini might have said: > >> Hello, >> >> today I made some test on a project I wrote some years ago. >> I upgraded sqlite library from version 3.4.2 to version 3.6.17. >> What I am really unable to understand is the time difference required >> to perform the same query using the exact same algorithm by the two >> libraries. >> >> SELECT * FROM table1 >> where table1 has 1 million rows and 10 columns (its an 80MB db). >> >> Version 3.4.2 takes about 5.06 seconds (average value) while version >> 3.6.17 takes about 7.28 seconds (average value). >> Could be a slowdown in the library for the complexity added over the >> years or does someone have another possible explanation? >> >> Thanks. > > Did you just relink your app or did you also migrate the data to a new > sqlite3 database? I think the migration command is: > > echo '.dump' | sqlite3 $db | sqlite3 $dbnew > > Maybe the internal database structure has changed? > > Mike > ___ > 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] Explanation
On Fri, 28 Aug 2009, Marco Bambini might have said: > Hello, > > today I made some test on a project I wrote some years ago. > I upgraded sqlite library from version 3.4.2 to version 3.6.17. > What I am really unable to understand is the time difference required > to perform the same query using the exact same algorithm by the two > libraries. > > SELECT * FROM table1 > where table1 has 1 million rows and 10 columns (its an 80MB db). > > Version 3.4.2 takes about 5.06 seconds (average value) while version > 3.6.17 takes about 7.28 seconds (average value). > Could be a slowdown in the library for the complexity added over the > years or does someone have another possible explanation? > > Thanks. Did you just relink your app or did you also migrate the data to a new sqlite3 database? I think the migration command is: echo '.dump' | sqlite3 $db | sqlite3 $dbnew Maybe the internal database structure has changed? Mike ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Will referential constraints be supported in the future ?
Hi all I think Sqlite is great, but I'm surprised referential integrity contraints have not been implemented a long time ago. Does anyone know if this is anywhere on the roadmap ? Best regards, Anders ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Regarding Memory Database
Can I ask you why do you need to allocate memsys5 memory pool in the first place? Why don't you use standard mallocs? Or even your own allocator? I just have a feeling that you're pursuing some goals very specific to your application and this kind of functionality is not necessary to anybody else. Pavel On Fri, Aug 28, 2009 at 12:19 AM, shankar m wrote: > Its not a problem in SQLite. I need a feature enhancement. > > Currently in SQLite i can allocate memory in a block. > > Example: > 2.5MB memory for memsys5 memory pool. Here 2MB i am using for Mem Db and > 0.5MB for heap and other DB cache. > > I want flexibility such that when i close mem db, the 2MB should be freed. > For this I need to configure SQLite with 2 Memory blocks. One with 2MB and > another 0.5MB. > > For doing this I have sent the modifications that needs to be done. > > Regards > Shankar > > > > > On Thu, Aug 27, 2009 at 4:40 PM, Pavel Ivanov wrote: > >> OK. So you have allocated memsys5 memory pool and now are complaining >> that this memory is not returned to OS? >> >> Sorry, but it's not quite understandable what do you want to achieve, >> what's the problem with it and how did you find that there's a >> problem. >> >> Pavel >> >> On Thu, Aug 27, 2009 at 6:11 AM, shankar m >> wrote: >> > I have allocated memory for SQLite heap and using memsys5 memory pool >> > algorithm to manage the heap. So if free is called it will be added to >> free >> > list of memsys5 memory pool. >> > >> > Regards >> > Shankar >> > >> > On Wed, Aug 26, 2009 at 7:51 PM, Pavel Ivanov >> wrote: >> > >> >> And I mean that you're wrong in this vision. SQLite doesn't keep >> >> memory in pools if it doesn't belong to cache necessary to some open >> >> connection. At least SQLite calls free() which of course doesn't >> >> necessarily mean that memory will be returned to OS. >> >> >> >> >> >> Pavel >> >> >> >> On Wed, Aug 26, 2009 at 10:02 AM, shankar m >> >> wrote: >> >> > I mean to say free to the OS so that it can use for other purpose. >> >> > >> >> > SQlite frees but maintains to the memory will be present in SQLite >> memory >> >> > pool or heap. Not to the OS. >> >> > >> >> > Regards >> >> > Shankar >> >> > >> >> > On Wed, Aug 26, 2009 at 7:15 PM, Pavel Ivanov >> >> wrote: >> >> > >> >> >> > SQLite currently does not support freeing of allocated memory >> >> >> > unless it is shutdown. >> >> >> >> >> >> It seams to me that you're wrong in this conclusion or elaborate >> >> >> please how did you come to it. >> >> >> In fact SQLite always frees all memory related to connection >> >> >> (including cache) when you close it. And you can clearly see it in >> >> >> sources... >> >> >> >> >> >> >> >> >> Pavel >> >> >> >> >> >> On Wed, Aug 26, 2009 at 12:57 AM, shankar m> > >> >> >> wrote: >> >> >> > Hi, >> >> >> > >> >> >> > I am using SQLite in a embedded system which has the following >> >> databases >> >> >> > 1. 64MB Flash stored Database for persistent storage >> >> >> > 2. 2 MB In-Memory Database. >> >> >> > >> >> >> > When the Memory Database is closed the 2MB should be returned to >> the >> >> >> > operating system. The 2 MB will be reallocated when the system >> wants >> >> to >> >> >> use >> >> >> > the mem db. SQLite currently does not support freeing of allocated >> >> memory >> >> >> > unless it is shutdown. >> >> >> > >> >> >> > >> >> >> > To implement the above requirement I am planning to do as follows >> >> >> > >> >> >> > In SQLite Page cache module i.e pcahe1.c handles the datbase >> buffering >> >> of >> >> >> > both persistent storage and in-memory storage. >> >> >> > To identify whether the database is mem or file there is a flag >> >> >> bPurgeable >> >> >> > in struct PCache1. >> >> >> > >> >> >> > The pages for cache are allocated in functions >> >> >> > 1. pcache1AllocPage >> >> >> > 2. pcache1FreePage >> >> >> > >> >> >> > Add 2 new configuration variables >> >> >> > 1. SQLITE_CONFIG_MEMDB - Enables separate memory DB with the >> default >> >> >> > values >> >> >> > 2. SQLITE_CONFIG_MEMSIZE- Configures the mem db with user >> specified >> >> >> > memory block. Similar to the config variable >> >> >> > SQLITE_CONFIG_PAGECACHE >> >> >> > >> >> >> > Using bPurgeable in pcache1AllocPage identify the mem db, If memdb >> >> then >> >> >> use >> >> >> > the mem block specified by the user. >> >> >> > The algorithm for the page allocation will be same as the list >> >> >> > implementation present for the SQLITE_CONFIG_PAGECACHE >> >> >> > >> >> >> > Please provide comments or suggestion. >> >> >> > >> >> >> > Thanks >> >> >> > Shankar >> >> >> > ___ >> >> >> > 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/sq
[sqlite] Explanation
Hello, today I made some test on a project I wrote some years ago. I upgraded sqlite library from version 3.4.2 to version 3.6.17. What I am really unable to understand is the time difference required to perform the same query using the exact same algorithm by the two libraries. SELECT * FROM table1 where table1 has 1 million rows and 10 columns (its an 80MB db). Version 3.4.2 takes about 5.06 seconds (average value) while version 3.6.17 takes about 7.28 seconds (average value). Could be a slowdown in the library for the complexity added over the years or does someone have another possible explanation? Thanks. -- Marco Bambini http://www.sqlabs.com http://www.creolabs.com/payshield/ ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Undefined Symbol: Tcl_CreateObjCommand
Hi guys, I'm trying to use sqlite 3.6.17 from a Tcl script in Linux, but as soon as I do "load libsqlite3.so" I get an error: "undefined symbol: Tcl_CreateObjCommand" Testing the script in Windows works fine. Anyone knows how to solve it? Thanks. -- Carlos Tasada Software Developer Farmers WIFE S.L Tel+34 971 730 777 Fax+34 971 730 729 www.farmerswife.com This e-mail and any attached files may contain confidential and/or privileged information. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional triggers
I sent this last night, but it didn't post on the list. Trying again to make sure this is working. -- Original Message -- Received: 08:40 PM MDT, 08/27/2009 From: cscs-sql...@usa.net To: General Discussion of SQLite Database Subject: Re: [sqlite] Conditional triggers Yes, you should be able to do this but I think your conditions need to be: when new.TypeID = 1 and when new.TypeID = 2 I'm sure you saw this, but note the WHEN clause and text in the document concerning when you can reference new and old values for the columns. http://www.sqlite.org/lang_createtrigger.html -- Original Message -- Received: 08:27 PM MDT, 08/27/2009 From: Dennis Volodomanov To: "sqlite-users@sqlite.org" Subject: [sqlite] Conditional triggers Hello all, Is it possible to create such an AFTER INSERT trigger that updates certain fields in a table based on the actual data being inserted? Let's say: CREATE TABLE abc(TypeID INTEGER) CREATE TABLE abcCount(TypeCountA, TypeCountB) CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when abc.TypeID == 1 */ BEGIN UPDATE abcCount SET TypeCountA=TypeCountA+1; END CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when abc.TypeID == 2 */ BEGIN UPDATE abcCount SET TypeCountB=TypeCountB+1; END Is something like that possible? I couldn't find any syntax construct I could put in place of the comment. Thanks in advance, Dennis ___ 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] Knowing required columns for Virtual Tables
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Allan Schrum wrote: > Does any one know of a way to know which columns are to be used in a virtual > table implementation for a particular query? There is an alternate approach. See http://www.sqlite.org/vtab.html#xcreate In particular if you declare a column to be HIDDEN then it won't be returned in queries unless the SQL explicitly asks for it. You can then declare all columns that are expensive to calculate as HIDDEN. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqX2NEACgkQmOOfHg372QQwnwCgh6a1Y77oxWDQ9NAFAElWO3x0 kA4An0XY++5SJpgSwlGI8R1B2arYKCjr =I+vY -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional triggers
Alejandro Ruiz-Oriol wrote: > Use old.TypeID==1 if you want the trigger to be fired when TypeID WAS 1 > before the update, or new.TypeID==1 if you want the trigger to be fired when > TypeID is updated to 1. > > Hope this help... > Thank you for the reply - my original problem wasn't exactly that, but appreciate your help in any case! Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Looking for a w_char alternative for sqlite3_get_table
A.J.Millan wrote: > Because some complex manipulations in my dBase, and probably because > my weakly knowledge of the SQL, currently I'm using extensively the > sqlite3_get_table function. > > Now I'm embraced in porting the application to Unicode and need some > like a sqlite3_get_table16 version who let use a zero-terminated > UTF-16 string in its 2nd parameter. There ain't no such thing. Now would be a good time to switch to prepared statements - see sqlite3_prepare, sqlite3_step, sqlite3_finalize, sqlite3_bind_*, sqlite3_column_* Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Looking for a w_char alternative for sqlite3_get_table
Hi list: Because some complex manipulations in my dBase, and probably because my weakly knowledge of the SQL, currently I'm using extensively the sqlite3_get_table function. Now I'm embraced in porting the application to Unicode and need some like a sqlite3_get_table16 version who let use a zero-terminated UTF-16 string in its 2nd parameter. Perhaps a seudo-code who let the same result using the standard API functions would be enough. Any help would be greatly appreciated. A.J.Millan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Problem invoking php functions from a trigger
Hello
AR if I run this php script
AR
AR *$dbh = new PDO('sqlite:/var/www/test.sqlite');
AR $sql="INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES (
AR '2221' , '2121' , '1212' , '1212' ) ";
AR $modulo=$dbh->query($sql);
AR print_r($dbh->errorInfo()); *
AR
AR from outside SQLiteManager I get this errorInfo():
AR
AR *Array
AR (
AR [0] => HY000
AR [1] => 1
AR [2] => no such function: test
AR )*
Being able to execute PHP functions from inside SQL is a bit of a bonus
feature. I imagine that PDO, which implements a subset of many SQL
databases' features, doesn't reach this far.
If you use the SQLite3 extension in PHP, you can register your own
functions. You would create the function in your PHP source, and then
register it when you open the database, and then call it from your SQL
statements.
Perhaps you could keep your "user_function" table, and query it to get the
PHP code for each function, eval it somehow and then register it with the
database connection.
There is more here:
http://www.php.net/manual/en/sqlite3.createfunction.php
I hope this helps.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conditional triggers
Use old.TypeID==1 if you want the trigger to be fired when TypeID WAS 1 before the update, or new.TypeID==1 if you want the trigger to be fired when TypeID is updated to 1. Hope this help... 2009/8/28 Dennis Volodomanov > Hello all, > > Is it possible to create such an AFTER INSERT trigger that updates certain > fields in a table based on the actual data being inserted? > > Let's say: > > CREATE TABLE abc(TypeID INTEGER) > CREATE TABLE abcCount(TypeCountA, TypeCountB) > > CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when abc.TypeID == 1 */ > BEGIN >UPDATE abcCount SET TypeCountA=TypeCountA+1; > END > > CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when abc.TypeID == 2 */ > BEGIN >UPDATE abcCount SET TypeCountB=TypeCountB+1; > END > > Is something like that possible? I couldn't find any syntax construct I > could put in place of the comment. > > Thanks in advance, > > Dennis > > ___ > 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] Problem invoking php functions from a trigger
Hi everybody,
I'm having a problem when I invoke a function developed in php from a
trigger.
I've been using SQLIteManager to develop and test my functions and
everything work's perfect.
I've created this function:
*INSERT INTO user_function ( funct_name , funct_type , funct_code ,
funct_num_args , base_id )
VALUES ( 'Test' , 1 , 'function test() { syslog(LOG_ALERT,"Hello trigger
world"); }' , 0 , 2 ) * *;*
Then I've created this trigger:
*CREATE TRIGGER Test AFTER
INSERT ON Test_table FOR EACH ROW BEGIN
SELECT test ( ) ;
END * *;*
And, if I insert a new row in the test_table (from SQLIteManager) everything
works and I get the message in syslog.
BUT!!
if I run this php script
*$dbh = new PDO('sqlite:/var/www/test.sqlite');
$sql="INSERT INTO Test ( Nombre , IP , MAC , Descripcion_Modulo ) VALUES (
'2221' , '2121' , '1212' , '1212' ) ";
$modulo=$dbh->query($sql);
print_r($dbh->errorInfo()); *
from outside SQLiteManager I get this errorInfo():
*Array
(
[0] => HY000
[1] => 1
[2] => no such function: test
)*
can anybody help me??
Regards
Chano
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Knowing required columns for Virtual Tables
Does any one know of a way to know which columns are to be used in a virtual table implementation for a particular query? At the xFilter() point the engine essentially knows what it is going to do and what columns it will use, but that information does not seem to be exposed anywhere. Certainly I know some of the columns used for the constraints, but I do not know all of them nor do I know all those required for processing the query. I can get significant improvement if I do not have to provide all possible columns for a row when I only need a few. Thanks, -Allan ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Conditional triggers
Hello all, Is it possible to create such an AFTER INSERT trigger that updates certain fields in a table based on the actual data being inserted? Let's say: CREATE TABLE abc(TypeID INTEGER) CREATE TABLE abcCount(TypeCountA, TypeCountB) CREATE TRIGGER CountTypeA AFTER INSERT ON abc /* when abc.TypeID == 1 */ BEGIN UPDATE abcCount SET TypeCountA=TypeCountA+1; END CREATE TRIGGER CountTypeB AFTER INSERT ON abc /* when abc.TypeID == 2 */ BEGIN UPDATE abcCount SET TypeCountB=TypeCountB+1; END Is something like that possible? I couldn't find any syntax construct I could put in place of the comment. Thanks in advance, Dennis ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] got a loop call of mallopt() when run sqlite_open()
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 chean xu wrote: > i got a loop call of mallopt() when run sqlite_open(), > anybody can help me? Start here: http://www.catb.org/~esr/faqs/smart-questions.html Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.9 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iEYEARECAAYFAkqXuGsACgkQmOOfHg372QSILwCguTzd2vAK8Hvls/HdBpXs3rsi 42MAoLrXpcUpC+G0+kGjG0sezNaTB6kG =kQkg -END PGP SIGNATURE- ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] got a loop call of mallopt() when run sqlite_open()
i got a loop call of mallopt() when run sqlite_open(), anybody can help me? (gdb) bt #0 0x15d01050 in mallopt () from /lib/libc.so.6 #1 0x15e2b400 in ?? () from /lib/libc.so.6 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
