Re: [sqlite] PHP: squelch warning and error messages
Hello L I know what this error/warning is. I *DO* error handling in my code. I L just don't want the message printed on the webpage. You can configure PHP to log error messages, rather than display them. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Importing CSV File Into Table
Hello TJM I am trying to import some data from a csv file to an existing table TJM called MasterChargeList. But this is failing with the error that the TJM table does not exist. This is on SQLite 3.6.4 (required to use this TJM version). See below for details: TJM sqlite .import /csv/iv_codes.csv MasterChargeList; TJM Error: no such table: MasterChargeList; Try it without the semi colon at the end. Lines that start with a . don't need a ; at the end. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is my posting?
Hello AO How can I know if my email actually is being considered by other AO members of the list or if it was never posted? Somehow this one made it through. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] max id mystery
Hello
EN I have an sqlite3 db that stores user-accounts (each user has an id);
You say you have a sqlite3 database, but it looks like you are using PHP's
sqlite 2 interface. Unless you have special requirements or limilations, I
would consider using the PDO interface for sqlite3.
Why not let the database handle the generation of user IDs? You seem to be
emulating an auto increment primary key. It is possible to get the last
generated ID back with sqlite_last_insert_rowid (or something similar in
the other interfaces) - it isn't something you need to know in advance.
EN $query = SELECT MAX(id) AS largestID FROM profiles;
EN $result_array = $database-arrayQuery($query, SQLITE_ASSOC);
EN foreach ($result_array[0] as $key = $value)
EN $max_id = $value;
EN $new_max_id = $max_id+1;
It may just be a matter of style, but I wouldn't use a foreach loop to get
one value out of an array. You could do the same with:
$query = SELECT MAX(id) AS largestID FROM profiles;
$result_array = $database-arrayQuery($query, SQLITE_ASSOC);
$new_max_id = $result_array[0][largestID] + 1;
EN $query =
EN 'INSERT INTO profiles (birthDate, company, country, email, facebook,
EN firstName, gender, id, lastName, skype, summary, twitter) ' .
EN 'VALUES (' . $php_data-dateOfBirth . '' .
EN ',' . $php_data-company . '' .
EN ',' . $php_data-country . '' .
EN ',em...@gmail.com' .
EN ',' . $php_data-facebook . '' .
EN ',' . $php_data-firstName . '' .
EN ',' . $php_data-gender . '' .
EN ',' . $new_max_id . '' .
EN ',' . $php_data-lastName . '' .
EN ',' . $php_data-skype . '' .
EN ',' . $php_data-summary . '' .
EN ',' . $php_data-twitter . ');';
EN }
It would be better to bind your pdp_data variables, so that you don't
leave yourself open to SQL injection attacks. The sqlite3 and PDO
interfaces let you do this.
EN The problem is as follows; The first id is 80; when the number of users
EN reaches 20 ( last id =100), the next id is never incremented! And new users
EN keep getting id=100,
EN although the DB primary key is incremented correctly;
Is there a reason for starting at 80?
If you put some error checking in your code, then that should let you know
where the problem is.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Conversion of SQLite Database to MySql format
Hello SS I have a very large (600 mb) SQLite database file SS I'm trying to convert it to MySql (.sql) file format on a windows platform. SS Does anyone know of a free conversion utility? SS Use any MySQL utility to dump the database as SQL commands into a text SS file. On a Mac I use 'Sequel Pro' but I don't know what platform SS you're using. SS Use a text editor to remove any LOCK and UNLOCK commands from the text SS file and make any other changes you want. Put a BEGIN before all the SS INSERT commands and a COMMIT after all the INSERT commands. SS Use the sqlite3 shell tool to '.read' the text file making your SQLite SS database. SS http://www.sqlite.org/download.html SS http://www.sqlite.org/sqlite.html SS I recommend a file extension of '.sqlite' for your SQLite database SS file if you haven't already picked one. Hard though it is to believe, the OP wants to go in the other direction. But doing a .dump of the SQLite database will give you the SQL commands needed to recreate the database. Use .output FILE to send output to FILE, and then .dump. You can then edit FILE using a text editor. The schema may need changing, depending on your column types and things like triggers, indexes and foreign keys. You should then be able to get MySQL to read in FILE. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Confused about Multiple Indexes
Hello MS CREATE TABLE tx (name TEXT, type INTEGER, seq INTEGER, seq_record TEXT, MS ...); MS CREATE INDEX IDX1 on tx(name ASC); MS CREATE INDEX IDX2 on tx(type, search_name ASC); The two indexes cover different columns, so they do different things. The indexes you need depend on the queries you will be doing, so there is no way I can tell if you need these indexes. Columns which are used in WHERE clauses are good candidates for indexing, generally speaking. You don't show a column called search_name in the CREATE TABLE line. I assume there is one. A more specific answer would require more information, but 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] Confused about Multiple Indexes
Hello MS The second index should be: MS CREATE INDEX IDX2 on tx(type, name ASC); MS What I had meant to ask was whether there is any benefit in having two MS indexes when one of the indexes is exactly within the other. MS IDX1 is index on 'name ASC' while IDX2 is an index on 'type, name ASC' - MS does this mean that in a sense IDX1 is a subset of IDX2 and can be removed? If you wanted to have just one index, rather than two, then you could have: CREATE INDEX idx ON tx(name ASC, type); With the columns in this order (name followed by type), the index will be used for queries which have either just name, or both name and type in their WHERE clauses. I think this is what this page is saying in sections 1.0 and 1.1: http://www.sqlite.org/optoverview.html If you had an index (type, name ASC), then a query which used column name, but didn't use column type would not get to use the index. So, yes you could get away with one index. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Hello BS select BS patient_id BS from BS table1 BS where BS age = 50 BS limit 6 BS union all BS select BS patient_id BS from BS table1 BS where BS age = 60 BS limit 4 You might want to wrap the two selects with limits inside subqueries: select patientID from ( select patientID from table1 where age = 50 limit 6 ) union all select patientID from ( select patientID from table1 where age = 60 limit 4 ); Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] union all with limit
Hello BS Thanks, I tried that and it gives no error, but only gives the first BS lot, not the bit after the union all. Ah. I hadn't tried with data. I don't know why the LIMIT affects the UNION ALL, when it is buried in a subquery. It doesn't affect a UNION (no ALL). In your real data and query, can a patient have more than one age? If not, then UNION and UNION ALL would produce the same results. sqlite create table table1 (patientID int, age int); sqlite insert into table1 (patientID, age) values (1, 50); sqlite insert into table1 (patientID, age) values (2, 50); sqlite insert into table1 (patientID, age) values (3, 50); sqlite insert into table1 (patientID, age) values (4, 50); sqlite insert into table1 (patientID, age) values (5, 50); sqlite insert into table1 (patientID, age) values (6, 50); sqlite insert into table1 (patientID, age) values (7, 50); sqlite insert into table1 (patientID, age) values (8, 60); sqlite insert into table1 (patientID, age) values (9, 60); sqlite insert into table1 (patientID, age) values (10, 60); sqlite insert into table1 (patientID, age) values (11, 60); sqlite insert into table1 (patientID, age) values (12, 60); This seems to work: select patientID from table1 where patientID in (select patientID from table1 where age=50 limit 6) union all select patientID from table1 where patientID in(select patientID from table1 where age=60 limit 4); 1 2 3 4 5 6 8 9 10 11 Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using sqlitejdbc-v056 with ant build file
Hello When I run a script, using sqlitejdbc-v056, with DROP, CREATE, and INSERT statements I get this error message java.sql.SQLException: no ResultSet available. The statement actually is successful as the data does appear in the database. It will be something to do with these statements not being queries. I don't know how you are calling these statements, but often there are two different methods/functions: - an exec function which doesn't expect a result set to be returned (DELETE, INSERT, UPDATE etc.) - a query function which does expect a result set (SELECT) It looks like the statement is being called with a query type function or method, where as the exec type one would be more suited to the statements you are calling. Maybe that will help, maybe not. Is ant talking directly to sqlite-jdbc, or is there some of your Java code in between (which you could modify)? Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of select result
Hello L Thanks Swithun. I dont have any supportive column in table to issue L order by. Can it be achieved by issuing an ordr by query on L row_id (used by sqlite internaly)? You could, I think. But it might be better style to create an INTEGER PRIMARY KEY AUTOINCREMENT column to do this explicitly. One advantage of this is that if your table gets so large that all the 64 bit integers have been used up, then you won't be able to insert any more rows. Without the AUTOINCREMENT, the database will start to reuse numbers from rows that got deleted. That at least, is what I understand from reading http://www.sqlite.org/autoinc.html and http://www.sqlite.org/lang_createtable.html Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] order of select result
Hello L Lets assume that we have a very simple without any indexing or Lconstraints. Now we have inserted some data to the table. When we do a Lsimple select query, is it guranteed that the rows will be retrieved Lin the same order as they are inserted? No. It may look like they are being selected in the order they were inserted. But there is no guarantee. So you should use an ORDER BY clause to get the order you want. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite documents in pdf format
Hello AR You can also bookmark html pages. AR I'm using pdf because I can comment and bookmark it to facilitate my AR reading process. I'm open to any other options. But it seems that AR there is no way to comment on chm (Let me know if I'm wrong). Then AR probably the solution is to convert html into pdf. How about Zotero (http://www.zotero.org/)? As they say: Zotero [zoh-TAIR-oh] is a free, easy-to-use Firefox extension to help you collect, manage, cite, and share your research sources. It lives right where you do your work—in the web browser itself. I haven't used it myself, but I've heard good things about it. Swithun.___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from PHP5?
Hello GG I'd like to use SQLite from some PHP5 scripts, either on Linux or GG Linux. A wise choice either way! GG What is the right way to set things up so that I can use a recent GG release of SQLite (apparently, the SQLite that comes with PHP by GG default is a 2.x release)? If you use PDO, then you get access to Sqlite 3.x databases: http://www.php.net/manual/en/ref.pdo-sqlite.php Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Accessing SQLite from PHP5?
Hello GG Could it be that the Www directory is off-limit to PHP scripts in GG write mode? FWIW, PDO can succesfully find and open db.sqlite: The directory itself should be writeable by the lighttpd user, as sqlite will want to create temporary journal files in the same directory. So being able to write to the database file isn't enough. You don't get the exception message because the exec method doesn't throw an exception. It puzzled me for a bit. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] recursive select in sqlite
Hello z i wonder is there RECURSIVE select function in sqlite? the background z for the question are: create table objects (id INTEGER PRIMARY KEY z AUTOINCREMENT, name text unique) create table tree(id int, child_id z int, PRIMARY KEY(id, child_id)) i want to draw the whole tree, is there z good solution for the function? If you want to store a tree structure in relational tables, then you could read up on things like this: http://articles.sitepoint.com/article/hierarchical-data-database The model on the second page is the one to go for. Hope this helps. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Encryption of sqlite DB
Hello KR What’s the simplest way to encrypt only certain rows in an sqlite DB? KR If there is no way to do this (for storing passwords etc), I would KR like to know the best way to encrypt the whole sqlite DB. (Prefer only KR encrypting some rows, but if this introduces complexity, I’m willing KR to encrypt the whole database) There is an encryption extension which costs money, from the same people that brought you SQLite. Or, assuming you mean you want to encrypt certain columns, you could do this from your application (C, PHP or whatever). If you have the clear text password as input, you can put it through an encryption function, and then use that as a parameter for a query, rather than the clear text. Or you could create a user defined function to call your encryption function, and then use this function in your SQL statements. Swithun.___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Passing a $dbHandle to a new page
Hello F Aha! now I am starting to understand the utility of that data.sqlite file! F Thanks again, Simon, and I will look for a PHP forum. Do you know a good F one? I've find that the PHP manual: http://www.php.net/manual/en/ answers almost all my questions. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] sqlite, php, and Mac OS X 10.6.1
Hello
PR try {
PR $dbHandle = new PDO('/Users/mymachine/sqlite:'.$user_db);
PR }
How about:
$dbHandle = new PDO('sqlite:/Users/mymachine/'.$user_db);
The sqlite: should be at the beginning of the DSN (Data Source Name), so
that PDO can recognise the name of the driver to use.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQL language question
Hello DRH The question is this: Should the no-op UPDATE statement (x=x) cause DRH the ON UPDATE SET NULL foreign key constraint to set t2.y to NULL or DRH not? I think MySQL knows if a row gets actually updated. If the values in a row don't change, then it says that no rows were updated. I was surprised at first, but I suppose it is quite handy to know if anything was really changed. To get round this, I found a suggestion that you have a dummy column in the table, and you make it default to 0 and then on each update, set it to 1 - dummy. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow SELECT query
Hello w I don't want the log to grow indefinitely, so I periodically call a method w which makes the following query: w SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999 w A DELETE query is executed on every result. How about something like: DELETE FROM log WHERE id IN (SELECT id FROM log ORDER BY timestamp DESC LIMIT 1,999); Which will only be one delete statement, instead of thousands. w I've added an index on the timestamp column: w CREATE INDEX IF NOT EXISTS log_idx ON log (id, timestamp) You should only need an index on the timestamp column for the SELECT statement to work. The id column is already indexed as it is the primary key. 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] mod_python sqlite: Doesnt the INSERT istance
Hello L the error is L OperationalError: unable to open database file L L can anyone fix it? L note: the db file has the 777 rights and my user has the file owner You might need to have write access to the directory containing the database file, as a journal file may be created temporarily in the same directory. Swithun. ___ 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] Subtotal SQL
Hello
AE I have many different ways of ordering these objects. Usually using
AE several sort parameters. I need a running sum of size that works
AE regardless of what order the objects are in.
I tried creating a UDF, but it seems that ordering is done after the
unordered results have been collected, so the running total column was not
increasing every time, as it should.
There probably is a sub query that would give you a sum of the rows up to
and including each row in the table, depending on different orderings. But
it would be quite slow if you had a lot of rows.
Then I thought of using the UDF on a sorted sub query, e.g.
SELECT my_sum(t2.val)
FROM (SELECT *
FROM table AS t1
ORDER BY t1.col1) AS t2
ORDER BY t2.col1;
where 'col1' is what ever column you want to sort on, and val is the
column to be summed.
Without the outer order by clause, the results were wrong. But now the
table is only sorted twice, rather than for each row in the table.
The UDF is just a function which has access to a variable using the
sqlite3_user_data function. The value of the column being summed is added
to an integer in this variable (a C struct), and then this new value is
returned using sqlite3_result_int.
It looks like this:
/* function */
void my_sum(sqlite3_context *ctxt, int argc, sqlite3_value **argv)
{
aggregate_ctxt *agg_ctxt;
agg_ctxt = (aggregate_ctxt *) sqlite3_user_data(ctxt);
agg_ctxt-total += sqlite3_value_int(argv[0]);
sqlite3_result_int(ctxt, agg_ctxt-total);
}
But this would be more easily done once the ordered results were in a 2D
array, i.e. outside of SQL/sqlite.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Generate scripts from SQLite database
Hello JD Is there some tool to generate scripts from SQLite database? I need to JD create scripts (create database, create table, triggers etc.) from JD existing database. From the command line tool, the command .schema will output the commands used to create the database and all tables and triggers. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Heirarchical queries question
Hello
D I'm trying to get my head around doing hierarchies in SQL. I've been
D Googling and it appears Oracle and MS SQL have some extensions to help, but
D I'm trying to figure out what can be done with 'plain' SQL.
With tree structures in table, I like to use a pair of coordinates (x and
y), which together identify the location of a node in the tree.
So, the database schema could look like this:
CREATE TABLE directories (
dir_id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
dir_name VARCHAR (255),
dir_x INTEGER,
dir_y INTEGER
);
CREATE INDEX x_idx ON directories (dir_x);
CREATE INDEX y_idx ON directories (dir_y);
CREATE INDEX xy_idx ON directories (dir_x, dir_y);
Traverse the tree in depth-first order, giving each node the first
coordinate when going down, and the second when going back up. The first
child of a node N's x coordinate is 1 more than the x coordinate of node N.
If a node N has no children, then the y coordinate of node N is 1 more than
the x coordinate of node N. If node N has several child nodes, then the y
coordinate of node N is 1 more than the y coordinate of the last child of
node N.
For a sample tree structure, similar to your example:
(path x-coord y-coord)
/ 1 16
/home 2 15
/home/swithun 3 8
/home/swithun/hierarchy 4 5
/home/swithun/mail 6 7
/home/user2 9 14
/home/user2/foo 10 13
/home/user2/foo/bar 11 12
the INSERT statements would look like:
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('', 1, 16);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('home', 2, 15);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('swithun', 3, 8);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('hierarchy', 4, 5);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('mail', 6, 7);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('user2', 9, 14);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('foo', 10, 13);
INSERT INTO directories (dir_name, dir_x, dir_y) VALUES ('bar', 11, 12);
To get the path and the number of subdirectories below that path, the
following query seems to produce the correct results:
SELECT GROUP_CONCAT('/', d2.dir_name) AS path,
d3.subdirectories
FROM directories AS d1
INNER JOIN directories AS d2
ON d2.dir_x = d1.dir_x
AND d2.dir_y = d1.dir_y
INNER JOIN (SELECT COUNT(*) - 1 AS subdirectories,
d4.dir_id AS dir_id
FROM directories AS d4
INNER JOIN directories AS d5
ON d5.dir_x = d4.dir_x
AND d5.dir_y = d4.dir_y
GROUP BY d4.dir_id) AS d3
ON d1.dir_id = d3.dir_id
GROUP BY d1.dir_id;
Table d1 is the driving table. d2 provides the ancestor directories for each
directory in d1, to generate the path. d3 is the count of how many
subdirectories are descendants (d5) of each directory in d4, then joined to
d1.
If you want to include the subdirectory in the count, remove the - 1.
It is a bit more complicated than the other suggestions, but the indexes
are only integers. You would need to write a script to generate the INSERT
statements.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Nested Inner Join Help
Hello EH Do the a2-style (for want of a better way of defining them) names EH exist outside the SQL statement which defines them? Tables and columns have fixed names, which you define when you write your database schema, e.g. CREATE TABLE myTable (id INT, value TEXT); But you can give these names aliases in your SQL statements: e.g. SELECT t1.id AS id1, t1.value AS val1 FROM myTable AS t1 INNER JOIN myTable AS t2 ON t1.id = t2.id; These aliases only exist in the statements that define what they are aliasing. You can't reuse them in other statements without again specifying what they are aliasing. If I've understood you, then what you should read up on is aliases. 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] search in archive
Hello KN On Fri, 19 Jun 2009 13:56:52 -0400, Rizzuto, Raymond KN raymond.rizz...@sig.com wrote: KN KN Is it possible to have a search feature for the KN archive? KN KN Which archive? I think Raymond means the sqlite-users archive. You could download all the txt.gz files, cat them together and then grep for what you want to find. You wouldn't have the threads, but it might be easier than checking all the threads via the web site. Perhaps it is possible to import the files into an email client which can recreate the messages and threads. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] setting a date in a BEFORE INSERT trigger
Hello
SC My goal is that on an insert only the insertedby value is provide. The
SC trigger will set that to the updatedby, insertedon and updatedon
SC fields. I searched the web and the only examples I could find was of
SC an AFTER INSERT, am I better off with that approach? I would think
SC not.
How about using default values when you create the table, e.g.
CREATE TABLE Customer (
CustomerId INTEGER PRIMARY KEY AUTOINCREMENT,
IsInSlideShow INTEGER NOT NULL,
Username CHAR(50) NOT NULL,
Password CHAR(50),
insertedby CHAR(50) NOT NULL,
instertedon DATE DEFAULT CURRENT_DATE,
updatedby CHAR(50) NOT NULL,
updatedon DATE DEFAULT CURRENT_DATE,
UNIQUE (username));
You can then do an insert as:
INSERT INTO Customer (insertedby, updatedby) VALUES('me', 'me');
i.e. pass the same value for both columns.
Hope this helps.
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] import / insert 120k records
Hello BM Whats the best way to import a large number of records ? BM 120K+ currently stored as an XML file. If your XML data is data-centric, then it should fit into one or more tables quite easily. If it can fit into one table, then you could use XSLT to convert the XML to CSV and .import that into the table you've created. If you need several tables, or because your number of records is large, you could use a SAX implementation (e.g. in Python or PHP) and parse the XML, and bind the values you get from it to placeholders in prepared statements. If the XML is more document-centric, then adjacency model and nested set were suggested recently as things to investigate for turning a tree structure into something tabular. There are probably lots of other ways to do it, depending on what languages you want to employ. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accessing php produced db from shell
Hello p i'm using sqlite3, but with php5 i had to install php5-sqlite. is the p sqlite i'm using with php5 on debian version 2? could this be the p problem? or is it something else? PHP uses SQLite2 in its normal extension. But the PDO extension uses SQLite3, so you can use that extension instead and also access the DB from the sqlite3 command line program. php -i | grep -i sqlite will tell you the versions that are being used on your machine. Swithun. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] accessing php produced db from shell
Hello
p i couldn't do php -i (bash: php: command not found), may be i have to
p install the cli php?
Yes, the cli php is optional.
p $base = new PDO(sqlite:$dbname, 0666, $err)
I don't think you need the other arguments for PDO. Something like:
if ($base = new PDO(sqlite:$dbname)) {
if (False === $base-exec(CREATE TABLE ...)) {
print didnt work;
}
} else {
print no db;
}
might work. The query method is more for getting results back, which you
don't need for creating a table. The exec method returns the number of
rows affected, which is probably 0 for CREATE TABLE, so testing for False
is what you want to do.
p $db-query()
Or, maybe $db should be $base, as in your example above?
Swithun.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
